Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- with 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;
- ---------------------------------
- Table definition os xx_sales are almost the same:
- Append-Only Columnar Table "tpcds.store_sales"
- Column | Type | Modifiers | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
- -----------------------+--------------+-----------+---------+--------------+------------------+-------------------+------------+-------------
- ss_sold_date_sk | integer | | plain | | zstd | 1 | 32768 |
- ss_sold_time_sk | integer | | plain | | zstd | 1 | 32768 |
- ss_item_sk | integer | not null | plain | | zstd | 1 | 32768 |
- ss_customer_sk | integer | | plain | | zstd | 1 | 32768 |
- ss_cdemo_sk | integer | | plain | | zstd | 1 | 32768 |
- ss_hdemo_sk | integer | | plain | | zstd | 1 | 32768 |
- ss_addr_sk | integer | | plain | | zstd | 1 | 32768 |
- ss_store_sk | integer | | plain | | zstd | 1 | 32768 |
- ss_promo_sk | integer | | plain | | zstd | 1 | 32768 |
- ss_ticket_number | bigint | not null | plain | | zstd | 1 | 32768 |
- ss_quantity | integer | | plain | | zstd | 1 | 32768 |
- ss_wholesale_cost | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_list_price | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_sales_price | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_ext_discount_amt | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_ext_sales_price | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_ext_wholesale_cost | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_ext_list_price | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_ext_tax | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_coupon_amt | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_net_paid | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_net_paid_inc_tax | numeric(7,2) | | main | | zstd | 1 | 32768 |
- ss_net_profit | numeric(7,2) | | main | | zstd | 1 | 32768 |
- Checksum: t
- Child tables: store_sales_1_prt_10,
- .........
- store_sales_1_prt_100,
- store_sales_1_prt_others
- Distributed by: (ss_item_sk, ss_ticket_number)
- Partition by: (ss_sold_date_sk)
- Options: appendonly=true, orientation=column, compresstype=zstd
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
