はじめに
本記事は個人メモです。
ベンチマークデータ以外、たいして有用な情報はありません。
以下の前記事にてTPC-DSベンチマークデータ 10TBをRedshiftの ra3.xlplus 2台構成に投入しました。
その環境を用いて実際にベンチマーク用のSQLを実行した結果をメモ代わりに残しておきます。
TPC-DSのER図や各テーブルの行数などは、前記事に記載してあります。
環境構成
- クラスタタイプ：プロビジョニング版(サーバレスではないもの）
- ノードタイプ：ra3.xlplus
- 台数：2台
- リージョン：東京リージョン
- クラスタのパラメータ：デフォルト(自動 WLM)
ベンチマーク用SQL
AWS公式から、query_0.sql を実行しました。
上記フォルダにはquery_0.sql から query_10.sql までのファイルが配置されています。これらは実行するsqlの順番がそれぞれランダム化されたもので、同時実行性能の影響をみる場合などに利用します。今回は純粋な性能を見たいので query_0.sql だけ実行しました。
結果
- query_0.sql の実行結果と、所要時間は以下の通りです。
- 1時間以上かかったクエリを太字にしています。
| num | query | duration |
|---|---|---|
| 1 | query96.tpl | 9 minutes |
| 2 | query7.tpl | 9 minutes |
| 3 | query75.tpl | 38 minutes |
| 4 | query44.tpl | 2 minutes |
| 5 part1 | query39.tpl | 16 seconds |
| 5 part2 | query39.tpl | 14 seconds |
| 6 | query80a.tpl | 10 minutes |
| 7 | query32.tpl | 6 seconds |
| 8 | query19.tpl | 43 seconds |
| 9 | query25.tpl | 3 minutes |
| 10 | query78.tpl | 8 hours |
| 11 | query86a.tpl | 3 minutes |
| 12 | query1.tpl | 3 minutes |
| 13 | query91.tpl | 36 seconds |
| 14 | query21.tpl | 11 seconds |
| 15 | query43.tpl | 3 minutes |
| 16 | query27a.tpl | 12 minutes |
| 17 | query94.tpl | 3 minutes |
| 18 | query45.tpl | 2 minutes |
| 19 | query58.tpl | 38 seconds |
| 20 | query64.tpl | 41 minutes |
| 21 | query36a.tpl | 15 minutes |
| 22 | query33.tpl | 1 minutes |
| 23 | query46.tpl | 5 minutes |
| 24 | query62.tpl | 5 minutes |
| 25 | query16.tpl | 13 minutes |
| 26 | query10.tpl | 2 minutes |
| 27 | query63.tpl | 1 minutes |
| 28 | query69.tpl | 4 minutes |
| 28 | query69.tpl | 4 minutes |
| 29 | query60.tpl | 1 minutes |
| 30 | query59.tpl | 8 minutes |
| 31 | query37.tpl | 1 minutes |
| 32 | query98.tpl | 16 seconds |
| 33 | query85.tpl | 3 minutes |
| 34 | query70a.tpl | 10 minutes |
| 35 | query67a.tpl | 4 hours |
| 36 | query28.tpl | 21 minutes |
| 37 | query81.tpl | 3 minutes |
| 38 | query97.tpl | 21 minutes |
| 39 | query66.tpl | 3 minutes |
| 40 | query90.tpl | 1 minutes |
| 41 | query17.tpl | 5 minutes |
| 42 | query47.tpl | 13 minutes |
| 43 | query95.tpl | 3 minutes |
| 44 | query92.tpl | 3 seconds |
| 45 | query3.tpl | 2 minutes |
| 46 | query51.tpl | 3 minutes |
| 47 | query35a.tpl | 3 minutes |
| 48 | query49.tpl | 2 minutes |
| 49 | query9.tpl | 22 minutes |
| 50 | query31.tpl | 4 minutes |
| 51 | query11.tpl | 58 minutes |
| 52 | query93.tpl | 26 minutes |
| 53 | query29.tpl | 11 minutes |
| 54 | query38.tpl | 48 minutes |
| 55 | query22a.tpl | 1 minutes |
| 56 | query89.tpl | 7 minutes |
| 57 | query15.tpl | 5 minutes |
| 58 | query6.tpl | 34 seconds |
| 59 | query52.tpl | 50 seconds |
| 60 | query50.tpl | 30 minutes |
| 61 | query42.tpl | 6 seconds |
| 62 | query41.tpl | 11 seconds |
| 63 | query8.tpl | 19 seconds |
| 64 | query12.tpl | 18 seconds |
| 65 | query20.tpl | 10 seconds |
| 66 | query88.tpl | 16 minutes |
| 67 | query82.tpl | 55 seconds |
| 68 | query23.tpl | 1 hours |
| 68 part2 | query23.tpl | 39 minutes |
| 69 | query14a.tpl | 21 minutes |
| 69 part2 | query14a.tpl | 18 minutes |
| 70 | query57.tpl | 18 minutes |
| 71 | query65.tpl | 9 minutes |
| 72 | query71.tpl | 3 minutes |
| 73 | query34.tpl | 52 seconds |
| 74 | query48.tpl | 1 minutes |
| 75 | query30.tpl | 2 minutes |
| 76 | query74.tpl | 37 minutes |
| 77 | query87.tpl | 19 minutes |
| 78 | query77a.tpl | 26 seconds |
| 79 | query73.tpl | 36 seconds |
| 80 | query84.tpl | 17 seconds |
| 81 | query54.tpl | 32 seconds |
| 82 | query55.tpl | 25 seconds |
| 83 | query56.tpl | 30 seconds |
| 84 | query2.tpl | 3 minutes |
| 85 | query26.tpl | 2 minutes |
| 86 | query40.tpl | 2 minutes |
| 87 | query72.tpl | 45 minutes |
| 88 | query53.tpl | 4 minutes |
| 89 | query79.tpl | 11 minutes |
| 90 | query18a.tpl | 17 minutes |
| 91 | query13.tpl | 10 minutes |
| 92 | query24.tpl | 49 minutes |
| 92 part2 | query24.tpl | 12 minutes |
| 93 | query4.tpl | 2 hours |
| 94 | query99.tpl | 6 minutes |
| 95 | query68.tpl | 2 minutes |
| 96 | query83.tpl | 28 seconds |
| 97 | query61.tpl | 2 minutes |
| 98 | query5a.tpl | 3 minutes |
| 99 | query76.tpl | 11 minutes |
詳細
- いくつかのクエリを気まぐれにピックアップしてみます。
- クエリは以下のサイトで再整形しました。
最長クエリ(query78.tpl)
-
最長クエリは、10番目に実施した以下のクエリです。
- query78.tpl
- 所要時間：8時間
-
クエリの中身は以下の通りです。
with
/* TPC-DS query78.tpl 0.10 */
ws as (
select
d_year AS ws_sold_year,
ws_item_sk,
ws_bill_customer_sk ws_customer_sk,
sum(ws_quantity) ws_qty,
sum(ws_wholesale_cost) ws_wc,
sum(ws_sales_price) ws_sp
from
web_sales
left join web_returns on wr_order_number = ws_order_number
and ws_item_sk = wr_item_sk
join date_dim on ws_sold_date_sk = d_date_sk
where
wr_order_number is null
group by
d_year,
ws_item_sk,
ws_bill_customer_sk
),
cs as (
select
d_year AS cs_sold_year,
cs_item_sk,
cs_bill_customer_sk cs_customer_sk,
sum(cs_quantity) cs_qty,
sum(cs_wholesale_cost) cs_wc,
sum(cs_sales_price) cs_sp
from
catalog_sales
left join catalog_returns on cr_order_number = cs_order_number
and cs_item_sk = cr_item_sk
join date_dim on cs_sold_date_sk = d_date_sk
where
cr_order_number is null
group by
d_year,
cs_item_sk,
cs_bill_customer_sk
),
ss as (
select
d_year AS ss_sold_year,
ss_item_sk,
ss_customer_sk,
sum(ss_quantity) ss_qty,
sum(ss_wholesale_cost) ss_wc,
sum(ss_sales_price) ss_sp
from
store_sales
left join store_returns on sr_ticket_number = ss_ticket_number
and ss_item_sk = sr_item_sk
join date_dim on ss_sold_date_sk = d_date_sk
where
sr_ticket_number is null
group by
d_year,
ss_item_sk,
ss_customer_sk
)
select
ss_customer_sk,
round(ss_qty /(coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 2) ratio,
ss_qty store_qty,
ss_wc store_wholesale_cost,
ss_sp store_sales_price,
coalesce(ws_qty, 0) + coalesce(cs_qty, 0) other_chan_qty,
coalesce(ws_wc, 0) + coalesce(cs_wc, 0) other_chan_wholesale_cost,
coalesce(ws_sp, 0) + coalesce(cs_sp, 0) other_chan_sales_price
from
ss
left join ws on (
ws_sold_year = ss_sold_year
and ws_item_sk = ss_item_sk
and ws_customer_sk = ss_customer_sk
)
left join cs on (
cs_sold_year = ss_sold_year
and cs_item_sk = ss_item_sk
and cs_customer_sk = ss_customer_sk
)
where
(
coalesce(ws_qty, 0) > 0
or coalesce(cs_qty, 0) > 0
)
and ss_sold_year = 2001
order by
ss_customer_sk,
ss_qty desc,
ss_wc desc,
ss_sp desc,
other_chan_qty,
other_chan_wholesale_cost,
other_chan_sales_price,
ratio
limit
100;
上記のSQL文をchatgpt(4.0)に解説させると以下の通りです。
このSQLクエリは、各販売チャネル（web_sales, catalog_sales, store_sales）からの年間商品販売情報を結合し、比較分析を行っています。
特定の年（2001年）で、店舗での購入が他の販売チャネルからの購入よりも多い顧客を抽出しています。
まず、最初の三つのサブクエリ（ws、cs、ss）は、それぞれの販売チャネルから商品（item）、顧客（customer）、販売年（year）、数量（qty）、卸売価格（wholesale_cost）、販売価格（sales_price）の情報を取得します。
これは、各チャネルの商品販売量を計算するためです。
また、web_returns、catalog_returns、store_returnsという返品テーブルを参照していますが、ここで重要なのは、返品された商品は除外するという条件（wr_order_number is null等）です。
つまり、これらのサブクエリは、返品を除いた販売データを取得しています。
メインのselect文では、これらのサブクエリを結合しています。
ここで重要なのは、結合条件として年、商品、顧客が等しいという条件が用いられている点です。
つまり、同一年、同一商品、同一顧客についての販売データを比較することを意味しています。
結合後、店舗（ss）での購入数量（ss_qty）と他のチャネル（wsとcs）での購入数量（ws_qtyとcs_qty）の比率（ratio）を計算します。
その他にも、各チャネルでの卸売価格と販売価格を計算しています。
where節で、他のチャネルからの購入数量が0より大きい、かつ、販売年が2001年であるデータを抽出しています。
つまり、2001年において、他のチャネルからも購入があった顧客についてのみ分析を行っています。
最後に、order by節とlimit節で、上位100件の結果を取得します。
このとき、ソート条件としては、顧客ID、店舗での購入数量、卸売価格、販売価格、他のチャネルでの数量、卸売価格、販売価格、そして比率（すべて降順）が用いられています。
これは、店舗での購入が多い顧客を特定し、それらの顧客が他のチャネルでどれだけ商品を購入しているかを分析するためです。
- 所感
- データセットで最も巨大なファクトテーブル3つを横断的に比較するクエリです。
- catalog_sales：144億行
- web_sales：72億行
- store_sales：288億行
- さらにJOINを2回やっていることが長時間化をもたらしています。
- データセットで最も巨大なファクトテーブル3つを横断的に比較するクエリです。
intersectを多用するクエリ(query38.tpl)
-
54番目に実施した以下のクエリを見てみます。
- |query38.tpl
- 所要時間：48分
-
クエリの中身は以下の通りです。
-- start template query38.tpl query 54 in stream 0
select /* TPC-DS query38.tpl 0.54 */ count(*) from (
select distinct c_last_name, c_first_name, d_date
from store_sales, date_dim, customer
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_customer_sk = customer.c_customer_sk
and d_month_seq between 1190 and 1190 + 11
intersect
select distinct c_last_name, c_first_name, d_date
from catalog_sales, date_dim, customer
where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
and d_month_seq between 1190 and 1190 + 11
intersect
select distinct c_last_name, c_first_name, d_date
from web_sales, date_dim, customer
where web_sales.ws_sold_date_sk = date_dim.d_date_sk
and web_sales.ws_bill_customer_sk = customer.c_customer_sk
and d_month_seq between 1190 and 1190 + 11
) hot_cust
limit 100;
上記のSQL文をchatgpt(4.0)に解説させると以下の通りです。
このクエリは、特定の期間（d_month_seqが1190から1201までの間）で店舗、カタログ、ウェブから商品を購入した顧客の一覧を取得し、それらの顧客の総数を計算するものです。
ただし、各顧客はその名前（姓と名）と購入日によって一意に識別されています。
具体的には、以下のような処理が行われています：
store_sales、date_dim、customerの3つのテーブルを結合し、特定の月の範囲内で店舗で購入した顧客の一覧を取得します。
同様に、catalog_sales、date_dim、customerを結合し、特定の月の範囲内でカタログで購入した顧客の一覧を取得します。
さらに、web_sales、date_dim、customerを結合し、特定の月の範囲内でウェブで購入した顧客の一覧を取得します。
これら3つのリストを比較し、すべての販売チャネルで購入した顧客（つまり、店舗、カタログ、ウェブのすべてで購入した顧客）を見つけます。
この結果から全体の顧客数をカウントします。
- 所感
- データセットで最も巨大なファクトテーブル3つを横断的に比較するクエリです。
- しかも 計算コストの高い集合系操作intersect(2つのテーブルの共通部分を見つける)を2回も使っています。
クエリ(query79.tpl)
- 89番目に実施した以下のクエリを見てみます。
- query79.tpl
- 所要時間：11分
select /* TPC-DS query79.tpl 0.89 */
c_last_name,c_first_name,substring(s_city,1,30),ss_ticket_number,amt,profit
from
(select ss_ticket_number
,ss_customer_sk
,store.s_city
,sum(ss_coupon_amt) amt
,sum(ss_net_profit) profit
from store_sales,date_dim,store,household_demographics
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_store_sk = store.s_store_sk
and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
and (household_demographics.hd_dep_count = 0 or household_demographics.hd_vehicle_count > 2)
and date_dim.d_dow = 1
and date_dim.d_year in (1999,1999+1,1999+2)
and store.s_number_employees between 200 and 295
group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
where ss_customer_sk = c_customer_sk
order by c_last_name,c_first_name,substring(s_city,1,30), profit
limit 100;
出力結果(一部)
上記のSQL文をchatgpt(4.0)に解説させると以下の通りです。
このSQLクエリは、顧客の名前、都市、チケット番号、クーポンの総額、および利益に関するデータを取得しています。
クエリは以下の主要な部分から構成されています。
(略)
ソートと制限: 最後に、c_last_name、c_first_name、s_city（最初の30文字）、および profit に基づいて結果をソートし、最初の100行のみを取得しています。
このクエリの目的は、特定の条件を満たす売上データに基づいて、顧客ごとのクーポン使用総額と利益を計算し、これらの情報と共に顧客の名前と都市を取得することです。
[クエリとロード]の画面キャプチャは以下のとおりです。
クエリ(query4.tpl)
- 93番目に実施した以下のクエリを見てみます。
- query4.tpl
- 所要時間：2時間
with /* TPC-DS query4.tpl 0.93 */ year_total as (
select c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total
,'s' sale_type
from customer
,store_sales
,date_dim
where c_customer_sk = ss_customer_sk
and ss_sold_date_sk = d_date_sk
group by c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
union all
select c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total
,'c' sale_type
from customer
,catalog_sales
,date_dim
where c_customer_sk = cs_bill_customer_sk
and cs_sold_date_sk = d_date_sk
group by c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
union all
select c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_flag
,c_birth_country customer_birth_country
,c_login customer_login
,c_email_address customer_email_address
,d_year dyear
,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total
,'w' sale_type
from customer
,web_sales
,date_dim
where c_customer_sk = ws_bill_customer_sk
and ws_sold_date_sk = d_date_sk
group by c_customer_id
,c_first_name
,c_last_name
,c_preferred_cust_flag
,c_birth_country
,c_login
,c_email_address
,d_year
)
select
t_s_secyear.customer_id
,t_s_secyear.customer_first_name
,t_s_secyear.customer_last_name
,t_s_secyear.customer_email_address
from year_total t_s_firstyear
,year_total t_s_secyear
,year_total t_c_firstyear
,year_total t_c_secyear
,year_total t_w_firstyear
,year_total t_w_secyear
where t_s_secyear.customer_id = t_s_firstyear.customer_id
and t_s_firstyear.customer_id = t_c_secyear.customer_id
and t_s_firstyear.customer_id = t_c_firstyear.customer_id
and t_s_firstyear.customer_id = t_w_firstyear.customer_id
and t_s_firstyear.customer_id = t_w_secyear.customer_id
and t_s_firstyear.sale_type = 's'
and t_c_firstyear.sale_type = 'c'
and t_w_firstyear.sale_type = 'w'
and t_s_secyear.sale_type = 's'
and t_c_secyear.sale_type = 'c'
and t_w_secyear.sale_type = 'w'
and t_s_firstyear.dyear = 1998
and t_s_secyear.dyear = 1998+1
and t_c_firstyear.dyear = 1998
and t_c_secyear.dyear = 1998+1
and t_w_firstyear.dyear = 1998
and t_w_secyear.dyear = 1998+1
and t_s_firstyear.year_total > 0
and t_c_firstyear.year_total > 0
and t_w_firstyear.year_total > 0
and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
> case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
> case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
order by t_s_secyear.customer_id
,t_s_secyear.customer_first_name
,t_s_secyear.customer_last_name
,t_s_secyear.customer_email_address
limit 100;
上記のSQL文をchatgpt(4.0)に解説させると以下の通りです。
with句での一時テーブル（Common Table Expression, CTE）作成
まず、with句を使用してyear_totalという名前の一時テーブル（CTE）を作成しています。
このテーブルは、顧客（customer）と売上データ（store_sales, catalog_sales, web_sales）および日付データ（date_dim）を結合し、顧客ごとに特定の計算式でyear_totalを算出しています。
このCTEは3つの異なるセクションで組み立てられており、それぞれがunion allで結合されています。
各セクションはstore_sales、catalog_sales、web_salesに対応しています。
主要なselect文
次に、この一時テーブルをさまざまな条件でフィルタリングしています。
顧客IDと売上の種類（sale_type）で複数の行を結合しています。
各顧客に対して、1998年と1999年の売上を比較しています。
特定の計算式で1999年の成長率を比較し、特定の条件を満たす顧客の情報を選択しています。
出力
最後に、特定の条件を満たす顧客のID、名前、姓、メールアドレスを出力しています。
結果は最大100行に制限されています。
このSQLクエリは、特定のビジネスロジックまたは分析要件に基づいて設計されていると思われます。
具体的には、1998年と1999年の間で成長率が最も高い販売チャンネル（店舗、カタログ、ウェブ）を持つ顧客を特定しています。
[クエリとロード]の画面キャプチャは以下のとおりです。
以上です。