データの完全な並べ替えが必要な場合。理論上は、プロジェクション内の式で GROUP BY を
使用することも可能ですが、集計の維持にはマテリアライズドビューの方が効果的です。
クエリオプティマイザも、単純な並べ替え、すなわち SELECT * ORDER BY x を使用する
プロジェクションの方をより活用しやすくなります。
ユーザーは、この式内で列のサブセットを選択することで、ストレージフットプリントを削減できます。
この例では、pickup_datetime で並べ替えられている、
sql.clickhouse.com で利用可能な New York Taxi Data
データセットを使用します。
乗客が運転手に 200 ドルを超える額のチップを支払った
すべてのトリップ ID を検索する、簡単なクエリを書いてみましょう。
SELECT
tip_amount,
trip_id,
dateDiff('minutes', pickup_datetime, dropoff_datetime) AS trip_duration_min
FROM nyc_taxi.trips WHERE tip_amount > 200 AND trip_duration_min > 0
ORDER BY tip_amount, trip_id ASC
ORDER BY に含まれていない tip_amount でフィルタリングしているため、ClickHouse はテーブル全体をスキャンする必要がありました。このクエリを高速化していきましょう。
元のテーブルと結果を保持するために、新しいテーブルを作成し、INSERT INTO SELECT を使ってデータをコピーします。
CREATE TABLE nyc_taxi.trips_with_projection AS nyc_taxi.trips;
INSERT INTO nyc_taxi.trips_with_projection SELECT * FROM nyc_taxi.trips;
ALTER TABLE nyc.trips_with_projection MATERIALIZE PROJECTION prj_tip_amount
プロジェクションを追加したので、改めてクエリを実行してみましょう。
SELECT
tip_amount,
trip_id,
dateDiff('minutes', pickup_datetime, dropoff_datetime) AS trip_duration_min
FROM nyc_taxi.trips_with_projection WHERE tip_amount > 200 AND trip_duration_min > 0
ORDER BY tip_amount, trip_id ASC
ALTER TABLE uk.uk_price_paid_with_projections
(ADD PROJECTION prj_gby_county
(
SELECT
county,
avg(price)
GROUP BY county
))
ALTER TABLE uk.uk_price_paid_with_projections
(MATERIALIZE PROJECTION prj_gby_county)
SETTINGS mutations_sync = 1
注記
上記の prj_gby_county プロジェクションのように、プロジェクション内で GROUP BY 句が使用されている場合、（隠された）テーブルの基盤となるストレージエンジンは AggregatingMergeTree になり、すべての集約関数は AggregateFunction に変換されます。これにより、適切な増分データ集約が保証されます。
SELECT
tables,
query,
query_duration_ms::String || ' ms' AS query_duration,
formatReadableQuantity(read_rows) AS read_rows,
projections
FROM clusterAllReplicas(default, system.query_log)
WHERE (type = 'QueryFinish') AND (tables = ['default.uk_price_paid_with_projections'])
ORDER BY initial_query_start_time DESC
LIMIT 2
FORMAT Vertical
行 1:
──────
tables: ['uk.uk_price_paid_with_projections']
query: SELECT
county,
avg(price)
FROM uk_price_paid_with_projections
GROUP BY county
ORDER BY avg(price) DESC
LIMIT 3
query_duration: 5 ms
read_rows: 132.00
projections: ['uk.uk_price_paid_with_projections.prj_gby_county']
行 2:
──────
tables: ['uk.uk_price_paid_with_projections']
query: SELECT
county,
price
FROM uk_price_paid_with_projections
WHERE town = 'LONDON'
ORDER BY price DESC
LIMIT 3
SETTINGS log_queries=1
query_duration: 11 ms
read_rows: 2.29 million
projections: ['uk.uk_price_paid_with_projections.prj_obj_town_price']
2行のセット。経過時間: 0.006秒
元のテーブル（とそのパフォーマンス）を維持するため、ここでも CREATE AS と INSERT INTO SELECT を使ってテーブルのコピーを作成します。
CREATE TABLE uk.uk_price_paid_with_projections_v2 AS uk.uk_price_paid;
INSERT INTO uk.uk_price_paid_with_projections_v2 SELECT * FROM uk.uk_price_paid;
ALTER TABLE uk.uk_price_paid_with_projections_v2
ADD PROJECTION projection_by_year_district_town
(
SELECT
toYear(date),
district,
town,
avg(price),
sum(price),
count()
GROUP BY
toYear(date),
district,
town
)
SELECT
toYear(date) AS year,
round(avg(price)) AS price,
bar(price, 0, 1000000, 80)
FROM uk.uk_price_paid_with_projections_v2
GROUP BY year
ORDER BY year ASC
SETTINGS optimize_use_projections=0
SELECT
toYear(date) AS year,
round(avg(price)) AS price,
bar(price, 0, 1000000, 80)
FROM uk.uk_price_paid_with_projections_v2
GROUP BY year
ORDER BY year ASC
SELECT
toYear(date) AS year,
round(avg(price)) AS price,
bar(price, 0, 2000000, 100)
FROM uk.uk_price_paid_with_projections_v2
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year ASC
SETTINGS optimize_use_projections=0
SELECT
toYear(date) AS year,
round(avg(price)) AS price,
bar(price, 0, 2000000, 100)
FROM uk.uk_price_paid_with_projections_v2
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year ASC
SELECT
town,
district,
count() AS c,
round(avg(price)) AS price,
bar(price, 0, 5000000, 100)
FROM uk.uk_price_paid_with_projections_v2
WHERE toYear(date) >= 2020
GROUP BY
town,
district
HAVING c >= 100
ORDER BY price DESC
LIMIT 100
SETTINGS optimize_use_projections=0
SELECT
town,
district,
count() AS c,
round(avg(price)) AS price,
bar(price, 0, 5000000, 100)
FROM uk.uk_price_paid_with_projections_v2
WHERE toYear(date) >= 2020
GROUP BY
town,
district
HAVING c >= 100
ORDER BY price DESC
LIMIT 100
