Skip to content

Instantly share code, notes, and snippets.

@simpl1g

simpl1g/prewhere.sql

Last active September 10, 2021 15:15
Show Gist options
  • Save simpl1g/c82ed2b41eefc0f1c57bc1112b78035f to your computer and use it in GitHub Desktop.
Save simpl1g/c82ed2b41eefc0f1c57bc1112b78035f to your computer and use it in GitHub Desktop.
Download ZIP
where/prewhere
Raw
prewhere.sql
ENGINE = ReplicatedSummingMergeTree
PARTITION BY toYYYYMMDD(date)
PRIMARY KEY app_id, date, install_date
SELECT install_date, SUM(install_count)
FROM analytics_grouped_stats_shard
PREWHERE (attribution_network IN ('Facebook Ads')) AND (date >= toDate('2021-08-01')) AND (date < today())
AND (install_date >= toDate('2021-08-01')) AND (install_date <= toDate('2021-08-31'))
GROUP BY install_date
Selected 133/5017 parts by partition key, 107 parts by primary key, 97942/411618 marks by primary key, 97942 marks to read from 18986 ranges
Read 801820317 rows, 3.74 GiB in 0.599862449 sec
SELECT install_date, SUM(install_count)
FROM analytics_grouped_stats_shard
PREWHERE (attribution_network IN ('Facebook Ads')) AND (date >= toDate('2021-08-01')) AND (date < today())
WHERE (install_date >= toDate('2021-08-01')) AND (install_date <= toDate('2021-08-31'))
GROUP BY install_date
Selected 133/5017 parts by partition key, 107 parts by primary key, 97942/411616 marks by primary key, 97942 marks to read from 18986 ranges
Read 801820317 rows, 2.28 GiB in 0.851374643 sec
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment