This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:features:rollups:optimization [2012/02/22 22:31] doc |
admin:features:rollups:optimization [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 56: | Line 56: | ||
| === Simple count distinct === | === Simple count distinct === | ||
| + | |||
| + | <code> | ||
| + | ----------------------------------- SUMMARY ----------------------------------- | ||
| + | Select count(distinct GENDER) | ||
| + | from INDIVIDUALS; | ||
| + | |||
| + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) | ||
| + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO | ||
| + | ----------------------------------- DETAILS ----------------------------------- | ||
| + | Aggregate INDIVIDUALS_BY_DEMO using GENDER for COUNT(DISTINCT GENDER) on 1; | ||
| + | Return COUNT(DISTINCT INDIVIDUALS_BY_DEMO.GENDER); | ||
| + | -------------------------------------------------------------------------------</code> | ||
| + | |||
| + | === Simple count distinct with criteria === | ||
| <code> | <code> | ||
| Line 73: | Line 87: | ||
| </code> | </code> | ||
| - | === Simple count distinct with criteria === | + | === Simple grouped count === |
| <code> | <code> | ||
| Line 90: | Line 104: | ||
| CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | ||
| ------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | ||
| - | </code> | + | </code> |
| - | === Simple grouped count === | + | === Simple grouped count with criteria === |
| <code> | <code> | ||
| Line 112: | Line 126: | ||
| CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | ||
| ------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | ||
| - | </code> | + | </code> |
| - | === Simple grouped count with criteria === | + | === Count grouped by joined table === |
| <code> | <code> | ||
| Line 140: | Line 154: | ||
| </code> | </code> | ||
| - | === Count grouped by joined table === | + | === Count grouped by joined table with criteria === |
| <code> | <code> | ||
| Line 167: | Line 181: | ||
| UNSIGNED INTEGER); | UNSIGNED INTEGER); | ||
| ------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | ||
| - | </code> | ||
| - | |||
| - | === Count grouped by joined table with criteria === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- SUMMARY ----------------------------------- | ||
| - | Select STATES.DESCRIPTION, \ | ||
| - | count(*) \ | ||
| - | from LIST \ | ||
| - | join STATES on LIST.STATE = STATES.STATE \ | ||
| - | where CITY = 'Boulder' \ | ||
| - | group by STATES.DESCRIPTION | ||
| - | |||
| - | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | ||
| - | Optimization: MDKQUAL, AGGREGATION, STARSCHEMA, ROLLUP | ||
| - | Notes: Table LIST translated to Rollup Table LIST_GEO | ||
| - | ----------------------------------- DETAILS ----------------------------------- | ||
| - | Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET | ||
| - | Build cache {C1} as (SELECT DESCRIPTION, STATE FROM STATES) | ||
| - | Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1 | ||
| - | Retrieve {C1} using PK_STATE = LIST_GEO.STATE | ||
| - | Pass to queue {1} [STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)] | ||
| - | Sort {1} for GROUP BY [STATES.DESCRIPTION ASC] | ||
| - | Retrieve {1} sequentially | ||
| - | Return STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | |||
| </code> | </code> | ||