Cohort Query Processing

0.36 MB·

by  Dawei Jiang

Cohort Query Processing
Dawei Jiang† Qingchao Cai‡ Gang Chen† H. V. Jagadish§
Beng Chin Ooi‡ Kian-Lee Tan‡ Anthony K. H. Tung‡
†ZhejiangUniversity ‡NationalUniversityofSingapore §UniversityofMichigan
†{jiangdw, cg}@zju.edu.cn ‡{caiqc, ooibc, tankl, atung}@comp.nus.edu.sg §[email protected]
6
1
0 ABSTRACT Executing this query against a sample dataset (of which
2 Table 1 shows some records) results in Table 2, where each
Modern Internet applications often produce a large volume
tuplerepresentstheaveragegoldthatusersspenteachweek.
y of user activity records. Data analysts are interested in
The results seem to suggest that there was a slight drop in
a cohort analysis, or finding unusual user behavioral trends,
shopping, and then a partial recovery. However, it is hard
M in these large tables of activity records. In a traditional
to draw meaningful insights.
database system, cohort analysis queries are both painful
Therearetwomajorsourcesthatcanaffecthumanbehav-
4 to specify and expensive to evaluate. We propose to ex-
ior[9]: 1)aging, i.e., peoplebehavedifferentlyastheygrow
tend database systems to support cohort analysis. We do
olderand2)social changes,i.e., peoplebehavedifferentlyif
] so by extending SQL with three new operators. We devise
B thesocieties theylivein aredifferent. Inourin-gameshop-
threedifferentevaluation schemesfor cohort queryprocess-
ping example, players tend to buy more weapons in their
D ing. Twoofthemadoptanon-intrusiveapproach. Thethird
initial game sessions than they do in later game sessions -
approachemploysacolumnarbasedevaluationschemewith
.
s optimizationsspecificallydesignedforcohortqueryprocess- thisis theeffect of aging. On theotherhand,social change
c may also affect the players’ shopping behavior, e.g., with
ing. Ourexperimentalresultsconfirmtheperformanceben-
[ new weapons being introduced in iterative game develop-
efits of our proposed columnar database system, compared
ment, players may start to spend again in order to acquire
4 against the two non-intrusive approaches that implement
these weapons. Cohort analysis, originally introduced in
v cohort queries on top of regular relational databases.
2 Social Science, is a data analytical technique for assessing
8 1. INTRODUCTION the effects of aging on human behavior in a changing soci-
1 ety[9]. Inparticular,itallowsustoteaseaparttheeffectof
0 Internet applications often accumulate a huge amount of aging from the effect of social change, and hence can offer
0 activity data representing information associated with user more valuable insights.
. actions. Such activity data are often tabulated to provide With cohort analytics, social scientists study the human
1
insights intothe behavior of users in order to increase sales behavioraltrendinthreesteps: 1)groupusersintocohorts;
0
andensureuserretention. Toillustrate,Table1showssome 2)determinetheageofuseractivitiesand3)computeaggre-
6
samplesofarealdatasetcontaininguseractivitiescollected gationsforeach(cohort,age)bucket. Thefirststepemploys
1
from a mobile game. Each tuple in this table represents the so called cohort operation to capture the effect of so-
:
v a user action and its associated information. For example, cial differences. Social scientists choose a particular action
Xi tuple t1 represents that player 001 launched the game on e (called the birth action) and divide users into different
2013/05/19 in Australia in a dwarf role. groups (called cohorts) based on thefirst time (called birth
r An obvious solution to obtain insights from such activity time) that users performed e. Each cohort is then repre-
a
data is to apply traditional SQL GROUP BY operators. For sentedbythetimebin(e.g.,day,weekormonth)associated
example, if we want to look at the players’ shopping trend with the birth time 1. Each activity tuple of a user is then
in terms of the gold (the virtual currency) they spent, we assigned to the same cohort that this user belongs to. In
may run thefollowing SQL query Qs. the second step, social scientists capture the effect of aging
by partitioning activity tuples in each cohort into smaller
SELECT week, Avg(gold) as avgSpent
sub-partitions based on age. The age of an activity tuple t
FROM GameActions
is theduration between thebirth time of that user and the
WHERE action = "shop"
time that user performed t. Finally, aggregated behavioral
GROUP BY Week(time) as week
measure is reported for each (cohort, age) bucket.
Backtoourin-gameshoppingexample,supposewechoose
launch as thebirth action and week as thecohort time bin
interval, the activity tuples of player 001 are assigned to
2013-05-19 launch cohort since the activity tuple t (called
1
birthactivitytuple)indicatesthatplayer001firstlaunched
thegame atthat week. Wefurtherpartition activity tuples
1The interval of thetime bin is chosen to ensure that there
arenosignificantsocialdifferencesoccurredinthattimebin.
1
Table 1: Mobile Game Activity Table Table2: ResultsofQ
s
player time action role country gold week avgSpent
t1 001 2013/05/19:1000 launch dwarf Australia 0 2013-05-19 50
t2 001 2013/05/20:0800 shop dwarf Australia 50 2013-05-26 45
t3 001 2013/05/20:1400 shop dwarf Australia 100 2013-06-02 43
t4 001 2013/05/21:1400 shop assassin Australia 50 2013-06-09 42
t5 001 2013/05/22:0900 fight assassin Australia 0 2013-06-16 45
t6 002 2013/05/20:0900 launch wizard UnitedStates 0
t7 002 2013/05/21:1500 shop wizard UnitedStates 30
t8 002 2013/05/22:1700 shop wizard UnitedStates 40
t9 003 2013/05/20:1000 launch bandit China 0
t10 003 2013/05/21:1000 fight bandit China 0
2) extend SQL to support this generalized cohort analysis
Table 3: Cohort Report for Shopping Trend
in thispaper.
cohort age(weeks)
There are two limitations in the standard social science
1 2 3 4 5
style cohort analysis. First, social scientists typically an-
2013-05-19(145) 52 31 18 12 5
2013-05-26(130) 58 43 31 21 alyze a whole dataset. This is because the datasets used
2013-06-02(135) 68 58 50 are usually small and are specifically collected for a certain
2013-06-09(140) 80 73 cohort analysis task. As such, there is no mechanism for
2013-06-16(126) 86 extracting a portion of users or activity tuples for cohort
analysis. Whilethistask seemstrivial, it hastobehandled
1 2 3 4 5 withcareasanaiveselectionmayleadtoincorrectanswers!
5/19/2013 Referring to our running example (from Table 1), suppose
wechooselaunch asthebirthaction, andweareinterested
5/26/2013 to perform a cohort analysis on those tuples where time
6/2/2013 > 2013/05/22:0000. Now, the resultant subset of tuples
is {t ,t }. However, we no longer can perform any cohort
5 8
6/9/2013 analysisasthebirthactivitytuplet ,i.e.,theactivitytuple
1
6/16/2013 representingplayer001firstperforminglaunch,hasbeenre-
moved. Second,social scientists useonlythetimeattribute
toidentifycohorts. Thisisbecausetimeisconsideredtobe
0 102030405060708090 thekeyattributethatdeterminessocialchange. However,it
ispossiblethatsomeotherattributes,suchascountry,may
Figure 1: Launch Cohort Shopping Trend also have a significant effect on social differences, in which
case, it may beinteresting to perform cohorts based on the
country attribute. Thus, it would be desirable to provide
in2013-05-19launchcohortintosub-partitionsidentifiedby
support for a more general cohort analysis task.
age. Asanexample,theactivitytuplet ispartitionedinto
2
Inthis paper,we makethefollowing contributionsto ad-
theweek1agesub-partition. Finally,wereporttheaverage
dress theabove issues.
gold spent for each (cohort, age) bucket shown in Table 3
and visualized in Figure 1. Each rowin Figure 1 represents • We define the important problem of cohort analytics
the shopping trend of a cohort measured at different ages. in the context of a DBMS.
For example, column k (under the age section) reports the
• Weintroduceanextendedrelationtomodeluseractiv-
average expenditures of the cohorts at the kth week (i.e.,
ity datafor cohort analytics, andintroducethreenew
age = k).
operators for manipulating the extended relation and
By looking at each row horizontally, we can see the ag-
composing cohort queries. Two of the operators are
ing effect, i.e., players spent more gold on buying weapons
designedforextractingasubsetofactivitiesforcohort
ontheirinitialgamesessionsthantheirlatergamesessions.
analysis, and the last one is designed for producing
Forexample,forthe2013-05-19cohort,theamountofgold
aggregates over arbitrary attribute combinations. We
spent was 52 in the first week, but there is a clear declin-
show that cohort queries can be expressed elegantly
ing trend as time passes. On theotherhand,by comparing
andconciselyusingthedatamodelandthenewlypro-
different rows (i.e., reading rows vertically), we can observe
posedoperators. Wealso showhowmorecomplicated
that thedrop-off trend seems to becomes less severe. Com-
data analytics tasks can be expressed using a mix of
paringrows1and2inourexample,weobservethatforthe
traditional SQLclauses andthenewly proposed oper-
same column, the value of row 2 is larger than that of row
ators.
1. Thissuggeststhattheiterativegamedevelopmentindeed
• We build a columnar based cohort query engine, CO-
didabetterjobofretainingplayerenthusiasmastheyaged,
HAHA, which implements multiple optimizations for
an insight which cannot be drawn from OLAP style results
efficient cohort queryprocessing.
in Table 2.
While the classic cohort analysis we presented so far has • We design a benchmark study to compare COHANA
been very useful in many analytic applications such as re- againstalternativenon-intrusiveevaluationschemesin
tentionanalysis[1],wehopeto1)generalizetheapproachso termsofcohortqueryprocessingperformance. Theex-
that it can be applied to a wider range of applications and perimental results show that COHANA is two orders
2
WITH birth AS( birthTuples AS ( cohortT AS (
SELECT p, Min(t) as birthTime SELECT p, c as cohort, birthTime SELECT p, a, cohort, birthRole, gold
FROM D role as birthRole TimeDiff(D.t, birthTime) as age
WHERE a = "launch" FROM D, birth FROM D, birthTuples
GROUP BY p WHERE D.p = birth.p AND WHERE D.p = birthTuples.p),
), D.t = birth.birthTime), (c)
(a) (b)
cohortSize AS ( SELECT cohort, size, age, Sum(gold)
SELECT cohort, Count(distinct p) as size FROM cohortT, cohortSize
FROM cohortT WHERE cohortT.cohort = cohortSize.cohort
GROUP BY cohort birthRole = "dwarf" AND a = "shop" AND age > 0
), GROUP BY cohort, age
(d) (e)
Figure 2: The SQL query Q of our example analysis task.
s
superior to its mostly optimized counterpart, demon- To speed up the processing of the analysis task, we can
stratingthenecessityofextendingdatabasesystemsto adopt a materialized view (MV) approach that stores some
cater to cohort analytics, rather than simply running intermediate results. For example, we can materialize the
an SQL statement over a traditional DBMS. intermediate table cohortT produced by the sub-query in
The rest of the paper is organized as follows: Section 2 Qs (Figure 2(c)) as follows.
presents the SQL based and the materialized view based
CREATE VIEW MATERIALIZED cohorts as cohortT
approachesforprocessingcohortqueries. Section3presents
the foundations of cohort analysis. Section 4 presents our
proposedcolumnardatabasebasedschemeforcohortquery With cohorts, we can express thequery QS in a simpler
SQLstatementconsistingofasinglesub-query(Figure2(d))
processing. Section 5 reports the experimental results. We
andanouterquery(Figure2(e)). Theperformanceofthere-
presentrelatedworkinSection6andconcludeinSection7.
sultingSQLexpressionisalsoimprovedsinceitonlyinvolves
a single join. However,the materialized view approach also
2. NON-INTRUSIVEAPPROACHESTOCO-
suffers from a numberof issues.
HORTANALYTICS
• The cost of generating the MV is still high since it
Aleast intrusiveapproach tosupportingcohort analytics
involvestwo joins (Figure 2(b) and 2(c)).
istouseanexistingrelationalDBMSandexpressthecohort
analysistaskasaSQLquery. Weillustratesuchanapproach • The storage space for the MV is huge if the approach
using the following cohort analysis task: is used as a general cohort query processing strategy.
Example 1. Giventhelaunchbirthactionandtheactiv- Figure 2(c) only includes a single calculated birth at-
itytableasshowninTable1(denotedasD),forplayerswho tributebirthRoleasitistheonlyattributeappearing
play the dwarf role at their birth time, cohort those players in the birth selection condition (i.e., the condition of
based on their birth countries and report the total gold that playing as the dwarf role at birth time) of the analy-
country launch cohorts spent since they were born. sis task. However, if other calculated birth attributes
are also involved in the birth selection condition, we
Figure 2 shows the corresponding SQL query Qs for this needtoincludethoseattributesintheMVaswell. In
task. To save space, we use p, a, t, c abbreviations re-
the extreme case, every possible birth attribute shall
spectivelytodenotetheplayer,action,time,andcountry
be included in the MV, doubling the storage space as
attributeinTable 1. TheQs employsfoursub-queries(i.e., compared to theoriginal activity table.
Figure 2(a) – Figure 2(d)) and one outer query (i.e., Fig-
ure2(e))toproducetheresults. Overall,thisSQLapproach • The MV only answers cohort queries introduced by
performs poorly for threereasons: launch birth action. If another birth action (e.g.,
shop) is used, one more MV is required. Obviously,
• The SQL statement Qs is verbose, and its complexity this per birth action per MV approach does not scale
renders it proneto mistakes.
even for a small number of birth actions due to the
• TheSQLstatementQsrequiresmanyjoinstoperform cost of MV construction and maintenance.
theanalysis task. Aswe shall see in our experimental
• Thequeryperformanceisstillnotoptimal. Bythedef-
study, such a query processing scheme can be up to 5
inition of the analysis task, if a player does not play
ordersofmagnitudeslowerthanourproposedsolution.
as dwarf role when that player is born, we should ex-
• It requires manual tuning. For example, one may no- cludeallactivitytuplesofthatplayerintheresultset.
tice that we can push the selection condition (i.e., Ideally, this filtering operation can be performed by
birthRole = "dwarf")fromtheouterquery(Figure2(e)) simply checking the single birth activity tuple of that
totheinnersub-query(Figure2(c))toreducethesize player. If the birth activity tuple indicates that the
of intermediate tables. Ideally, such an optimization player is not qualified, we can safely skip all activity
can be performed by an intelligent optimizer. How- tuples of that player without further checking. How-
ever, our evaluation shows that few database systems ever,asshowninFigure2(e),theMVapproachneeds
can perform such an optimization. to, unnecessarily, checkeach activity tupleof a player
3
to perform the filtering operation (i.e., comparing the of useri if and only if
valueinbirthRoleattributeagainstdwarf). Building
an index on the birthRole attribute cannot improve ti,e = minπAt(σAu=i∧Ae=e(D)) if σAu=i∧Ae=e(D)6=∅
(cid:26) −1 otherwise
the situation much since index look up will introduce
too many random seeks on large activity tables. where π and σ are the standard projection and selection
operators.
3. COHORTANALYSIS FOUNDATIONS Definition 2. Given an activity table D, and a birth ac-
In this paper, we seek to extend an existing relational tione∈Dom(Ae),atupledi,e ∈D iscalledthebirthactiv-
ity tuple of user i if and only if
database system to support cohort analytics. This section
presents the data model, which includes a central new con- di,e[Au]=i∧di,e[At]=ti,e
cept of an activity table, and the proposed new cohort op-
erators. Since (Au,At,Ae) is the primary key of D, we conclude
Weusethetermcohort torefertoanumberofindividuals thatfor each useri, thereisonlyonebirthactivity tupleof
who havesome common characteristic in performing a par- i in D for any birth action e that i performed.
ticularactionforthefirsttime;weusethisparticularaction
and the attribute values of the common characteristics to Definition 3. Giventhebirthtimeti,e,anumericalvalue
identify theresulting cohort. For example, agroup of users g is called theage of user i in tuple d∈D, if and only if
wcohmomfirosntlcohgairanc(ttehreistpiacr)tiicsuclaarllaedctitohne)i2n012501J5anJuaanruyarylo(gtihne d[Au]=i∧ti,e>=0∧g=d[At]−ti,e
cohort. Similarly customers who make their first purchase
The concept of age is designed for specifying the time
in the United States form a United States purchase cohort.
point toaggregate thebehavioralmetric ofa cohort. In co-
Broadlyspeaking,cohortanalysisisadataexplorationtech-
hort analysis, we calculate themetric only at positive ages.
niquethatexamineslongitudinalbehavioraltrendsofdiffer-
Thatis,iftheageofanuserinatupleisnegative,thattuple
ent cohorts since they were born.
will be excluded from the final report. The activity tuple
with a positive age is called an age activity tuple. Further-
3.1 Data Model
more, in practical applications, the age g is normalized by
Werepresentacollectionofactivitydataasaninstanceof a certain time unit such as a day,week or month. Without
an activity relation, aspecial relation whereeach tuplerep- loss of generality, we assume that the granularity of g is a
resentstheinformationassociatedwithasingleuseractivity. day.
We will also call an activity relation an activity table. In Consider the example activity relation in Table 1. Sup-
thispaper,thetwoterms,i.e.,activityrelation andactivity pose we use the action launch as the birth action. Then,
table are used interchangeably. theactivitytuplet1 isthebirthactivitytupleofplayer001,
Formally,anactivitytableD isarelation withattributes and the birth time is 2013/05/19:1000. The activity tuple
Au,At,Ae,A1,...,An wheren≥1. Au isastringuniquely t2 is an age tuple of player 001 produced at age 1.
identifying a user; Ae is also a string, representing an ac- 3.3 Cohort Operators
tion chosen from a pre-definedcollection of actions, and At
records the time at which Au performed Ae. Every other We now present operations on a single activity table. In
attribute in D is a standard relational attribute. Further- particular,weproposetwonewoperatorstoretrieveasubset
more, an activity table has a primary key constraint on of activity tuples for cohort analysis. We also propose a
(Au,At,Ae). Thatis,eachusericanonlyperformaspecific cohort aggregation operator for aggregating activity tuples
action e once at each time instant. As exemplified in Table for each (cohort, age) combination. As we shall see, these
1,thefirstthreecolumnscorrespondtotheuser(Au),times- three operators enable us to express a cohort analysis task
tamp(At)andaction(Ae)attribute,respectively. Roleand inaveryconciseandelegantwaythatiseasytounderstand.
Country are dimension attributes, which respectively spec-
3.3.1 Theσb Operator
ify the role and the country of player Au when performing C,e
Ae at At. Following the two dimension attributes is gold, The birth selection operator σCb,e is used to retrieve ac-
a measure attribute representing the virtual currency that tivity tuples of qualified users whose birth activity tuples
player Au spent for this action. We shall continue to use satisfy a specific condition C.
Table1asourrunningexamplefordescribingeach concept
in cohort analysis. Definition 4. Given an activity table D, the birth selec-
tion operator σb is defined as
C,e
3.2 BasicConcepts ofCohortAnalysis
σCb,e(D)={d∈D|i←d[Au]∧C(di,e)=true}
We present three core concepts of cohort analysis: birth
where C is apropositional formula and e isa birth action.
action,birthtimeandage. Givenanactione∈Dom(Ae),
the birth time of user i is the first time that i performed e
Consider the activity relation D in Table 1. Suppose we
or -1 if i never performed e, as shown in Definition 1. An
wanttoderiveanactivitytablefromDwhichretainsallac-
actioneiscalledabirthactionifeisusedtodefinethebirth
tivitytuplesofuserswhowerebornfromperforminglaunch
time of users.
actioninAustralia. Thiscanbeachievedwiththefollowing
expression, which returns {t ,t ,t ,t ,t }.
1 2 3 4 5
Definition 1. Given an activity table D, and a birth ac-
tion e∈Dom(Ae), a time value ti,e is called the birth time σcbountry=Australia,launch(D)
4
3.3.2 Theσg Operator inTable1isassignedtotheAustralialaunchcohort,player
C,e
The age selection operator σg is used to generate an 002isassignedtotheUnitedStateslaunchcohortandplayer
C,e
activity table from D which retains all birth activity tuples 003 is assigned to theChina launch cohort.
inD butonlyasubsetofageactivitytupleswhichsatisfya After assigning users to cohorts, in the second step, we
condition C. divide activity tuples (including both the birth activity tu-
ples andtheage activity tuples)into thesame cohorts that
Definition 5. GivenanactivitytableD,theageselection theuser belonged to for aggregation.
operator σg is defined as
C,e
Definition 6. Given an activity table D, the cohort ag-
σCg,e(D)={d∈D|i←d[Au]∧ gregation operator γLc,e,fA is defined as
((d[At]=ti,e)∨(d[At]>ti,e∧C(d)=true))} γLc,e,fA(D)={(dL,g,s,m)|
where C is a propositional formula and e is a birth action. Dg ←{(d,l,g)|d∈D∧i←d[Au]
For example, suppose shop is the birth action, and we ∧l=di,e[L]∧g=d[At]−ti,e}
want to derive an activity table which retains all birth ac- ∧(dL,g)∈πl,g(Dg)
tivity tuples in Table 1 but only includes age activity tu-
ples which indicate users performing in-game shopping in ∧s=Count(πAuσdg[l]=dL(Dg))
all countries but China. The following expression can be ∧m=fA(σdg[l]=dL∧dg[g]=g∧g>0(Dg))
used to obtain the desired activity table.
where L is a cohort attributes set, e is a birth action and
σagction=shop∧country6=China,shop(D) fA is a standard aggregation function with respect to the
attribute A.
Theresultsetoftheaboveselectionoperationis{t ,t ,t ,t ,t }
2 3 4 7 8
where t2 is the birth activity tuple of player 001, t3 and t4 Insummary,thecohortaggregation operatortakesanac-
are the qualified age activity tuples of player 001. The ac- tivity table D as input and produces a normal relational
tivity tuples t7 and t8 are the birth activity tuple and the table Ras output. Each row in theoutputtable Rconsists
qualified age activity tupleof player 002. offourparts(dL,g,s,m),wheredListheprojectionofbirth
A common requirement in specifying σCg,e operation is activitytuplesontothecohortattributessetLandidentifies
thatweoftenwanttoreferencetheattributevaluesofbirth the cohort, g is the age, i.e., the time point that we report
activity tuples in C. For example, given the birth action theaggregates,sisthesizeofthecohort,i.e.,thenumberof
shop, wemaywanttoselect ageactivitytupleswhoseusers usersinthecohortspecifiedbydL,andmistheaggregated
perform in-game shopping at the same location as their measure produced by the aggregate function fA. Note that
country of birth. We introduce a Birth() function for this we only apply fA on age activity tupleswith g>0.
purpose. GivenanattributeA,foranyactivitytupled,the
Birth(A) returns the value of attribute A in d[Au]’s birth 3.3.4 PropertiesofCohortOperators
activity tuple: We note that the two selection operators, σb and σg ,
C,e C,e
Birth(A)=di,e[A] are commutative if they involvethe same birth action.
where i=d[Au] and e is the birth action. σCb,eσCg,e(D)=σCg,eσCb,e(D) (1)
In our running example, suppose shop is the birth ac-
Based on this property, we can, as we shall see in Section
tion, and we want to obtain an activity table which retains
4, pushthebirthselection operatordown thequeryplanto
all birthactivity tuplesbutonly include age activity tuples
optimize cohort queryevaluation.
whichindicatethatplayersperformedshoppinginthesame
role as they were born. The following expression is used to 3.4 The Cohort Query
retrieve thedesired results. Given an activity table D and operators σCb,e, σCg,e, πL,
σrgole=Birth(role),shop(D) and γLc,e,fA,a cohort queryQ:D→R can beexpressed as
acomposition of thoseoperators thattakes D as inputand
Theresult set of theaboveoperation is{t2,t3,t7,t8}where produces a relation R as output with the constraint that
t2 and t7 are the birth activity tuples of player 001 and thesamebirthactioneisusedforallcohortoperatorsinQ.
player 002, respectively, and t3 and t8 are the qualified age To specify a cohort query, we propose to use the following
activity tuples. SQL-styleSELECT statement.
3.3.3 Theγc Operator
L,e,fA SELECT ... FROM D
We now present the cohort aggregation operator γLc,e,fA. BIRTH FROM action = e [ AND σCb,e ]
This operator produces cohort aggregates in two steps: 1) [ AGE ACTIVITIES IN σg ]
C,e
cohort users and 2) aggregate activity tuples. COHORT BY L
In the first step, given an activity table D with its at-
tribute set A and a birth action e, we pick up a cohort In the above syntax, e is the birth action that is speci-
attribute set L⊂A such that L∩{Au,Ae}=∅ and assign fied by the data analyst for the whole cohort query. The
eachuseritoacohortcspecifiedbydi,e[L]. Essentially,we order of BIRTH FROM and AGE ACTIVITIES IN clauses is ir-
divide users into cohorts based on the projection of users’ relevant,andthebirthselection(i.e.,σb )andageselection
C,e
birth activity tuples ontoa specified cohort attribute set. (i.e., σg ) clauses areoptional. Wealso introducetwo key-
C,e
In our running example, suppose launch is the birth ac- words AGEand COHORTSIZE for dataanalysts toretrievethe
tionandthecohortattributesetisL={country},player001 calculated columns produced by γc in the SELECT list.
L,e,fA
5
Notethatexceptforprojection,wedisallowotherrelational COHANA
operatorssuchasσ(i.e.,SQLWHERE)andγ(i.e.,SQLGROUP Query Parser EQxeuceurtyo r
BY),andbinaryoperatorslikeintersectionandjoin,inaba-
Results
sic cohort query.
With the newly developed cohort operators, the cohort Catalog Storage
Manager
analysis task presented in Example 1 can be expressed by
the following query:
Figure 4: COHANA Architecture
Q1: SELECT country, COHORTSIZE, AGE, Sum(gold) as spent
FROM D AGE ACTIVITIES IN action = "shop" As shown in Section 2, the MV approach stores each ac-
BIRTH FROM action = "launch" AND role = "dwarf" tivitytupleofuserialongwithi’sbirthattributes. Thus,to
COHORT BY country implement the birth selection operator, one can use a SQL
SELECT statement with a WHERE clause specifying the birth
3.5 Extensions selectionconditiononthematerializedbirthattributes. Sim-
ilarly,theageselectionoperatorcanbesimulatedbyaSQL
Our cohort query proposal can be extended in many di-
SELECT statement with a WHERE clause specifying the age
rections to enable even more complex and deep analysis.
selectionconditionalongwithanadditionalpredicatetoin-
First, it would be great to mix cohort queries with SQL
cludebirthactivitytuples. Thecohortaggregationoperator
queries in a single query. For example, one may want to
can be implemented by applying a SQL GROUP BY aggrega-
useaSQLquerytoretrievespecificcohorttrendsproduced
tionoperationonthejoinedresultsbetweenthecohortSize
by a cohort query for further analysis. If the cohort op-
table and the qualified age activity tuples.
eration is specified by multiple attributes (e.g., COHORT BY
As an example, Figure 3 demonstrates for Q1 of Exam-
time, country, role),onemaywanttoapplyaSQLCUBE
ple1thecorrespondencebetweenthethreeproposedcohort
operator on top of thecohort queryresults for OLAPanal-
operators and the equivalent SQL statements posed on the
ysis. This mixed querying requirement can be achieved by
MV built for the launch birth action. As in Figure 2, the
applyingthestandardSQLWITHclausetoencapsulateaco-
player, action and time attributes are respectively abbre-
hortqueryasasub-querythatcanbeprocessedbyanouter
viatedtop,a,andt. bc, br, btandagearefourattributes
SQL query. The following example demonstrates how to
additionallymaterializedalongwiththeoriginalactivityta-
use a mixed query to retrieve specific cohort spent trends
reported by Q for furtheranalysis. ble. The first three attributes, bc, br and bt, respectively
1
represent thebirth attributesfor country, role and time.
Itshould benotedthat theSQLstatementsof Figure3are
WITH cohorts AS (Q1)
separatedoutforeaseofexposition: onecanoptimizethem
SELECT cohort, AGE, spent FROM cohorts
by combining Figure 3(a) and 3(b) into a single SQLstate-
WHERE cohort IN ["Australia", "China"]
ment, as we do in experiments.
However,to ensurethecorrectness of amixed query,cer-
tain rules must be established to prevent SQL queries from
4. COHANA:COHORTQUERYENGINE
removingbirthactivitytuplesincidently. First,foramixed
query, the outermost query must be a SQL query. That is, To support cohort analytics with the newly designed co-
cohort queries can only serve as sub-queries inside a mixed hort operators, we present four extensions to a columnar
query. Furthermore, we only allow SQL queries (no mat- database: 1) a fine tuned columnar storage format for per-
ter sub-queriesor the outer query) to reference cohort sub- sistingactivitytables; 2)amodifiedtablescanoperator ca-
queries in the FROM or WHERE clauses and prohibit a cohort pableofskippingageactivitytuplesofunqualifiedusers;3)
sub-queryfromreferencingotherSQLorcohortsub-queries. a native efficient implementation of cohort operators; 4) a
Inaddition,amixedqueryshouldbeevaluatedintheorder queryplannercapableofutilizingthecohortoperatorprop-
of first cohort sub-queries and then others. This “cohort erty (i.e., Equation (1)) for optimization. We have imple-
query first”evaluation scheme ensuresthat no SQL queries mentedtheproposed techniquesin acolumnar based query
can incidentally remove birth activity tuples during query engine,COHAHA,forperformancestudy. Figure4presents
processing. the architecture of COHAHA which includes four modules:
Anotherextensionistointroducebinarycohortoperators parser, catalog, storage manager and query executor. The
(e.g., join, intersection etc.) for analyzing multiple activity firsttwomodulesaretrivial,andweshallfocusontheother
tables. Weleavethedetailsofevaluatingamixedqueryand two modules.
otherinterestingextensionsinafuturepaper. Intherestof
4.1 The Activity TableStorageFormat
this paper, we shall focus on the approaches for evaluating
a single cohort query overa single activity table. We store an activity table D in the sorted order of its
3.6 MappingCohortOperationstoSQLState- primary key (Au,At,Ae). This storage layout has two nice
properties: 1) activity tuples of thesame userare clustered
ments
together; we refer tothisas theclustering property;2) The
Before leaving this section, we shall demonstrate that activity tuples of each user are stored in a chronological
given a materialized view (MV) built for a specific birth order; this is called the time ordering property. With these
action, the proposed cohort operators can be implemented twoproperties,wecanefficientlyfindthebirthactivitytuple
by SQL sub-queries. This enable us to pose cohort queries of any user for any birth action in a single sequential scan.
composed of newly developed operators in the context of a Suppose the activity tuples of user i is stored between dj
non-intrusivemechanism. and d . To find the birth activity tuple of i for any birth
k
6
WITH birthView AS( ageView AS ( cohortSize AS ( SELECT cohort, size, age,
SELECT p, a, t, gold, SELECT * SELECT bc as cohort, Sum(gold) as spent
bc, bt, age FROM birthView Count(distinct p) FROM ageView, cohortSize
FROM MV WHERE a = "shop" OR as size WHERE cohort = bc
WHERE br = "dwarf" (t=bt AND a="launch") FROM birthView GROUP BY cohort, age
), ), GROUP BY bc),
(a) σb (b) σg (c) (d) γc
role=”dwarf”,launch action=”shop”,launch country,launch,Sum(gold)
Figure 3: The correspondence between cohort operators and SQL statements of the MV approach
action e, we just iterate over each tuple between dj and dk γccountry,launch,sum(Gold)
and return thefirst tuple db satisfying db[Ae]=e.
We employ a chunking scheme and various compression
σg
techniques to speed up cohort query processing. We first Action=“shop”,launch
horizontally partition the activity table into multiple data
chunkssuchthattheactivitytuplesofeachuserareincluded
σb
in exactly one chunk. Then, in each chunk, the activity Role=”dwarf”,launch
tuples are stored column by column. For each column in a
data chunk, we choose an appropriate compression scheme
for storing values based on thecolumn type. TableScan
FortheusercolumnAu,wechooseRun-Length-Encoding
(RLE) scheme. The values in Au is stored as a sequence of Figure 5: Query plan for Q
triples(u,f,n),whereuistheuserinAu,f isthepositionof 1
thefirstappearanceofuinthecolumn,andnisthenumber
of appearances of u in the column. We shall see in Section
in thebirth selection or age selection operation.
4.3,amodifiedtablescanoperatorcandirectlyprocessthese
Withtheabovetwoencodingschemes,thefinalrepresen-
triples and efficiently skip to the activity tuples of the next
tation of string columns and integer columns are arrays of
user if the birth activity tuple of the current user is not
integers within a small range. We therefore further employ
qualified with respect tothe birth selection condition.
integercompression techniquestoreducethestorage space.
For the action column Ae and other string columns, we Foreachintegerarray,wecomputetheminimumnumberof
employatwolevelcompression schemepresentedin[11]for
bits, denoted by n, to represent the maximum value in the
storingthevalues. Moredetailsofthisencodingschemecan
array,andthensequentiallypackasmanyvaluesaspossible
be found in [11]. For each such column A, we first build
into a computer word such that each value only occupies n
and persist a global dictionary which consists of the sorted
bits. Finally,wepersisttheresultingcomputerwordstothe
uniquevaluesofA. EachuniquevalueofAisthenassigned
storage device. This fixed-width encoding scheme is by no
a global-id, which is theposition of thatvaluein theglobal
means the most space-saving scheme. However, it enables
dictionary. Foreachdatachunk,thesortedglobal-idsofthe
thecompressed valuestoberandomlyreadwithout decom-
unique values of A in that chunk form a chunk dictionary.
pression. Foreachpositionintheoriginalintegerarray,one
Given the chunk dictionary, each value of A in that chunk
can easily locate the corresponding bits in the compressed
can be represented as a chunk-id, which is the position of
computerwordsandextractthevaluefrom thesebits. This
the global-id of that value in the chunk dictionary. The
feature is of vital importance for efficient cohort querypro-
chunk-ids are then persisted immediately after the chunk
cessing.
dictionaryinthesameorderastherespectivevaluesappear
in A. This two level encoding scheme enables the efficient
pruning of chunkswhere no users perform thebirth action. 4.2 Cohort Query Evaluation
For a given birth action e, we first perform a binary search
Thissectionpresentshowtoevaluateacohortqueryover
on the global index to find its global-id gi. Then, for each theactivity table compressed with thetechniquesproposed
data chunk,we perform a binary search for gi in thechunk in Section 4.1. We shall use the cohort query Q as our
1
dictionary. Ifgi isnotfound,wecansafely skipthecurrent running example. The overall query processing strategy is
data chunksince no users in thedata chunkperform e.
as follows. We first generate a logical query plan, and then
For At and other integer columns, we employ a two-level optimize it by pushing down the birth selections along the
delta encoding scheme which is similar to the one designed
plan. Next, the optimized query plan is executed against
for stringcolumns. Foreachcolumn Aofthistype,wefirst
eachdatachunk. Finally,allpartialresultsproducedbythe
store the MIN and MAX value of A for the whole activ-
third step are merged together to produce the final result.
ity table as the global range. Then, for each data chunk,
The final merging step is trivial and we shall only present
theMINand MAXvaluesareextractedasthechunkrange
thefirst threesteps.
from the segment of A in that chunk and persisted as well.
The cohort query plan we introduced in this paper is
Each value of the column segment is then finally stored as
a tree of physical operators consisting of four operators:
the delta (difference) between it and the chunk MIN value. TableScan, birth selection σb , age selection σg and co-
Similartotheencodingschemeforstringcolumns,thistwo- hort aggregation γc . LiCk,ee other columnarCd,eatabases,
leveldeltaencodingschemealsoenablestheefficientpruning L,e,fA
theprojection operation isimplementedinapre-processing
ofchunkswherenoactivitytuplesfallintherangespecified
step: we collect all required columns at query preparation
7
stage andthenpassthosecolumnstotheTableScan opera- Algorithm 1: σb (D) operator implementation
tor which retrieves thevalues for each column. C,e
Input:AdatachunkD andabirthactione
In the query plan, the root and the only leaf node are
the aggregation operator, γc , and the TableScan oper- 1 GetBirthTuple(d,e)
L,e,fA 2 i←d[Au]
ator, respectively, and between them is a sequence of birth 3 whiled[Au]=i∧d[Ae]6=edo
selection operators and age selection operators. 4 d←D.GetNext()
Then, we push down the birth selection operators along 5 returnd
the query plan such that they are always below the age se-
lection operators. This push-down optimization is always 6 Open()
7 D.Open()
feasible, since according to equation (1), we can arbitrarily
swap the order of σb and σg operators in any sequence 8 uc←∅
C,e C,e
consisting of thesetwooperators. Figure5shows thequery 9 GetNext()
plan for the cohort query of Q1. We always employ this 10 if uc has more activitytuples then
push-downoptimizationsince,asweshallseeinSection4.3, 11 returnD.GetNext()
aspecialdesignedTableScanimplementationcanefficiently 12 whilethere are more usersin the data chunk do
skip ageactivitytupleswithout furtherprocessing forusers 13 (u,f,n)←D.GetNextUser()
whosebirthactivitytuplesdonotsatisfythebirthselection 14 uc←u
15 d←D.GetNext()
condition. Therefore, the cost of evaluating birth selection
16 db←GetBirthTuple(d,e)
operators before age selection operators is always less than
17 Found←C(db)
the cost incurred from the reverse evaluation sequence in
18 if Foundthen
terms of thenumberof activity tuplesprocessed. 19 returnd
After pushing down birth selections, the resulting query
20 D.SkipCurUser()
plan will be executed against each data chunk. Before the
execution, we apply an additional filtering step by utiliz-
ing the Ae column’s two-level compression scheme to skip
datachunkswherenousersperformthebirthactione. The
concrete processing strategy is presented in Section 4.1. In
This section develops algorithms for the implementation
practice, wefindthatthisintermediatefilteringstepispar-
of cohort operators over the proposed storage format for
ticularly useful if the birth action is highly selective (i.e.,
activity tables.
only a few users performed that birth action).
Algorithm 1 presents the implementation of the birth se-
We will present theimplementation of thephysical oper-
lection operator σb . It employs an auxiliary function
ators in the rest of this section. C,e
GetBirthTuple(d,e) (line 1 – line 5) for finding the birth
activity tuple of user i=d[Au], given that d is the first ac-
4.3 The TableScan Operator tivitytupleof iin thedatachunkande isthebirth action.
We augment the standard TableScan operator of colum- TheGetBirthTuple() function findsi’sbirthactivitytuple
nar databases for efficient cohort query processing. The byiterating overeachnexttupled∈D andcheckswhether
modified TableScan operator performs scanning operation d belongs to i and whether d[Ae] is the birth action e (line
overthecompressed activitytablethatweproposedinSec- 3). The first activity tuple d matching the condition is the
tion4.1. Wemainlyaddtwoadditionalfunctionstoastan- required birth activity tuple.
dardcolumnardatabaseTableScanoperator: GetNextUser() To evaluate σCb,e, Algorithm 1 first opens the input data
and SkipCurUser(). The GetNextUser() function returns chunk D and initializes the global variable uc (line 7 – line
theactivitytupleblockofthenextuser;theSkipCurUser() 8) which points to the user currently being processed. In
skips theactivity tuplesof thecurrent user. the GetNext() function, we return the next activity tuple
The modified TableScan operator is implemented as fol- d of uc if uc is qualified with respect to the birth selection
lows. Foreachdatachunk,inthequeryinitialization stage, condition(line11). Ifuc’sactivitytuplesareexhausted,we
theTableScanoperatorcollectsall(compressed)chunkcolumns retrieve the next user block by calling the GetNextUser()
referencedinthequeryandmaintainsforeachchunkcolumn functionoftheTableScanoperator(line13). Then,wefind
a file pointer which is initialized to point to the beginning the birth activity tuple of the new user and check if it sat-
of that chunk column. The implementation of GetNext() isfies thebirth selection condition (line 16 – line 17). If the
function is identical to the standard TableScan operator of newuserisqualified,itsbirthactivitytuplewillbereturned;
a columnar database. otherwise all theactivity tuples of this user will beskipped
TheGetNextUser()isimplementedbyfirstretrievingthe using theSkipCurUser() function so that its next user can
next triple (u,f,n) of Au column and then advancing the be ready for processing. Therefore, one can continuously
file pointer of each other referenced column to the begin- calltheGetNext()functiontoretrievetheactivitytuplesof
ning of the column segment corresponding to user u. The users that are qualified with respect to the birth selection
SkipCurUser() isimplementedinasimilar way. Whenitis condition.
called,theSkipCurUser()functionfirstcalculatesthenum- The implementation of σCg,e is much simpler than σCb,e.
ber of remaining activity tuples of the current user, and Wealsoemploytheuserblockprocessingstrategy. Foreach
then advances the file pointers of all columns by the same user block, we first locate the birth activity tuple and then
number. returnthebirthactivitytupleandqualifiedageactivitytu-
ples.
Algorithm2presentstheimplementationofγc opera-
4.4 Cohort Algorithms L,e,fA
tor. The main logic is implemented in theOpen() function.
8
Algorithm 2: γ (D) operator implementation Thissectionpresentsaperformancestudytoevaluatethe
L,e,fA effectiveness of our proposed COHANA engine. We mainly
Input:AdatachunkD,abirthactione,anattributelistL
perform two sets of experiments. First, we study the ef-
1 Open()
2 D.Open() fectiveness of COHANA, and its optimization techniques.
3 Hc←∅ // Cohort size hash table In the second set of experiments, we compare the perfor-
4 Hg←∅ // Cohort metric hash table manceofdifferentqueryevaluationschemes. Weimplement
5 whilethere are more users inD do the SQL based approach and the materialized view based
6 (u,f,n)←D.GetNextUser()
approach on two relational databases: Postgres and Mon-
7 uc←u etDB, and compare the performance of these two systems
8 d←D.GetNext()
9 db←D.GetBirthTuple(d,e) with COHANA. For the two relational databases, we allow
10 if uc is qualified then them to use all the free memory for buffering, and leave all
11 Hc[db[L]]++ other settings as default.
12 whileuc has more qualified age activitytuples 5.1 Experimental Environment
do
13 g←d[At]−db[At] All experiments are run on a high-end workstation. The
14 updateHg[db[L]][g]withfA(d) workstation is equipped with a quad-core Intel Xeon E3-
1220 v3 3.10GHz processor and 8GB of memory. The disk
speedreported byhdparm is14.8GB/s forcachedreadsand
15 GetNext()
16 Retrievenextkey(c,g)fromHg 138MB/s for buffered reads.
17 return(c,g,Hc[c],Hg[c][g]) The dataset we used is produced by a real mobile game
application. The dataset consists of 30M activity tuples
contributed by 57,077 users worldwide from 2013-5-19 to
2013-06-26,andoccupiesadiskspaceof3.6GBinitsrawcsv
The function first initializes two hash tables Hc and Hg format. In addition to the required user, action and action
which respectively storethecohort sizeand perdatachunk timeattributes,wealsoincludethecountry,cityandroleas
aggregation result for each (cohort, age) partition (line 2 – dimensions and session length and gold as measures. Users
line 6). Then, the Open() function iterates over each user in the game played 16 actions in total, and we choose the
blockandupdatesHcforeachqualifieduser(determinedby launch, shopandachievement actionsasthebirthactions.
σb )andHgforallqualifiedageactivitytuples(determined In addition, we manually scale the dataset and study the
C,e
byσg ) (line10 –line14). To speedup thequeryprocess- performance of three cohort query evaluation schemes on
C,e
ing, we further follow the suggestions presented in [10, 11] different dataset size. Given a scale factor X, we produce a
and use array based hash tables for aggregation. In prac- datasetconsistingofXtimesusers. Eachuserhasthesame
tice, we find that the use of array-based hash tables in the activitytuplesastheoriginaldatasetexceptwithadifferent
inner loop of cohort aggregation significantly improves the user attribute.
performance since modern CPUs can highly pipeline array For the SQL based approach, we manually translate the
operations. cohort query into a SQL query as exemplified in Figure 2.
For the materialized view approach, we manually materi-
4.5 Optimizing forUserRetention Analysis alize the view using CREATE TABLE AS command. For each
birth action, we materialize the age and a birth attribute
Onepopularapplication of cohort analysis istoshowthe
set of time, role, country and city attribute in its material-
trend of user retention [1]. These cohort queries involve
ized view. This materialization scheme adds 15 additional
counting distinct number of users for each (cohort, age)
columns to the original table by performing six joins in to-
combination. This computation is very costly in terms of
memory for fieldswith alarge cardinality, suchas Au. For- tal. We also build a cluster index on the primary key and
indices on birth attributes. For COHANA, we choose the
tunately, our proposed storage format has a nice property
chunksize tobe 256K.
thattheactivitytuplesofanyuserareincludedin onlyone
chunk. Wetherefore implement aUserCount() aggregation 5.2 Benchmark Queries
function for the efficient counting of distinct users by per-
Wedesignfourqueries(describedwithCOHANA’scohort
formingcountingagainsteachchunkandreturningthesum
query syntax) for the benchmark by incrementally adding
of theobtained numbersas thefinal result.
the cohort operators we proposed in this paper. The first
4.6 AnalysisofQuery Performance query Q1 evaluates a single cohort aggregation operator.
ThesecondqueryQ2evaluatesacombinationofbirthselec-
Given therearenusersin theactivitytableD, each user
tion and cohort aggregation. The third query Q3 evaluates
produces m activity tuples, it can be clearly seen that, to
acombination ofageselection andcohort aggregation. The
evaluate a cohort querycomposed of σb , σg and γc
C,e C,e L,e,fA fourthqueryQ4evaluatesacombination ofallthreecohort
operators, the query evaluation scheme we presented so far
operators. For each query, we report the average execution
onlyneedstoprocessO(l×m)activitytuplesinasinglepass,
time of fiveruns for each system.
where l is the numberof qualified users with respect to the
Q1: For each country launch cohort, report the number
birth selection condition. Therefore, the query processing
ofretaineduserswhodidatleast oneactionsincetheyfirst
timegrowslinearlywithl,andthereforeapproachesoptimal
launched the game.
performance.
SELECT country, CohortSize, Age, UserCount()
FROM GameActions BIRTH FROM action = "launch"
5. A PERFORMANCESTUDY
COHORT BY country
9
Q2: Foreachcountrylaunchcohortborninaspecificdate SELECT country, COHORTSIZE, AGE, Avg(gold)
range, report thenumberof retained users who didat least FROM GameActions BIRTH FROM action = "shop"
one action since they first launched thegame. AGE ACTIVITIES IN action = "shop" AND AGE < g
COHORT BY country
SELECT country, COHORTSIZE, AGE, UserCount()
FROM GameActions BIRTH FROM action = "launch" AND
5.3 Performance Study ofCOHANA
time BETWEEN "2013-05-21" AND "2013-05-27"
COHORT BY country Inthissectionwereportonasetofexperimentsinwhich
we vary chunk size and birth/age selection condition and
Q3: For each country shop cohort, report the average
investigate how COHANA adaptsto such variation.
gold they spent in shopping since they made first shop in
the game.
5.3.1 EffectofChunkSize
SELECT country, COHORTSIZE, AGE, Avg(gold)
Figures 6 and 7 respectively present the storage space
FROM GameActions BIRTH FROM action = "shop"
COHANA requires for the activity table compressed with
AGE ACTIVITIES IN action = "shop"
different chunk sizes, and the corresponding query perfor-
COHORT BY country
mance. It is clearly seen from Figure 7 that increasing the
Q4: Foreachcountryshopcohort,reporttheaveragegold chunk size also augments storage cost. This is because an
they spent in shopping in their birth country where they increase in the size of a chunk will lead to more players in-
were born with respect to the dwarf role in a given date cluded in that chunk. As a result, the number of distinct
range. valuesinthecolumnsofeachchunkalsoincreases, whichin
turnrequiresmorebitsforencodingvalues. Wealsoobserve
SELECT country, COHORTSIZE, AGE, Avg(gold)
that cohort queries can beprocessed slightly faster undera
FROM GameActions BIRTH FROM action = "shop" AND
smaller chunk size than a larger one. This is expected as
time BETWEEN "2013-05-21" AND "2013-05-27" AND
fewer bytes are read. However, for large datasets, a larger
role = "dwarf" AND
chunksize can bea betterchoice. Forexample,at scale 64,
country IN ["China", "Australia", "United States"]
COHANA processes Q1 and Q3 most efficiently under 1M
AGE ACTIVITIES IN action="shop" AND country=Birth(country) chunksize. This is because theprocessing of Q1 and Q3at
COHORT BY country
scale 64 isdominated bydisk accesses, whose granularity is
In order to investigate the impact of the birth selection normallya4KBblock. Comparedwithalargechunksize,a
operator and the age selection operator on the query per- smalloneleadstomorepartoftheneighbouringcolumnsto
formance of COHANA, we further design two variants of be simultaneously read when reading a compressed chunk
Q1 and Q3 by adding to them a birth selection condition column, and hence results in a longer disk read time and
(resulting in Q5 and Q6) or an age selection condition (re- a lower memory efficiency due to the memory contention
sulting in Q7 and Q8). The details of Q5-Q8 are shown between the useful columns and their unused neighbours
below. within thesame chunk.
Q5: For each country launch cohort, report the number
ofretaineduserswhodidatleastoneactionduringthedate 5.3.2 EffectofBirthSelection
range [d1; d2] since they first launched the game. In Section 4.6, we claim that the running time of CO-
HANA is bounded by O(n) where n is the total number
SELECT country, COHORTSIZE, AGE, UserCount()
of qualified users. This experiment studies the query per-
FROM GameActions
formance of COHANA with respect to the birth selection
BIRTH FROM action = "launch" AND time BETWEEN d1 AND d2
selectivity. We run Q5 and Q6, which are respectively a
COHORT BY country
variant of Q1 and Q3, by fixing d1 to be the earliest birth
Q6: Foreachcountryshopcohort,reporttheaveragegold day,andincrementingd2byonedayeachtime. Thedataset
they spent in shopping during the date range [d1; d2] since used in this experiment is at scale 1.
they made their first shop in the game. Figure8presentstheprocessingtimesofQ5andQ6which
are respectively normalized by that of Q1 and Q3. The cu-
SELECT country, COHORTSIZE, AGE, Avg(gold)
mulative distribution of user births is also given in this fig-
FROM GameActions
ure. Wedonotdifferentiatethebirthdistributionsbetween
BIRTH FROM action = "shop" AND time BETWEEN d1 AND d2
the birth actions of launch and shop, as the birth distri-
AGE ACTIVITIES IN action = "shop"
butions with respect to both birth actions are similar. It
COHORT BY country
can be clearly observed from this figure that the process-
Q7: Foreachcountrylaunchcohortwhoseageislessthan ing time of Q5 highly coincides with the birth distribution.
g,report thenumberof retained users who did at least one We attribute this coincidence to the optimization of push-
action since they first launched the game. ing down the birth selection operator and the refined birth
selection algorithm which is capableof skippingunqualified
SELECT country, COHORTSIZE, AGE, UserCount()
users. The processing time of Q6, however, is not very sen-
FROM GameActions BIRTH FROM action = "launch"
sitivetothebirthdistribution. ThisisbecauseinQ6,users
AGE ACTIVITIES in AGE < g
arebornwithrespecttotheshopaction,andthereisacost
COHORT BY country
infindingthebirthactivitytupleforeachuser. Thiscostis
Q8: For each country shop cohort whose age is less than avoided in Q5 as the first activity tuple of each user is the
g,reporttheaveragegoldtheyspentinshoppingsincethey birth activity tuple of this user (recall that the first action
made their first shop in the game. each user performed is launch).
10