Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save RajenDharmendra/58db39d086b32e7dd208822480bbcacc to your computer and use it in GitHub Desktop.
Save RajenDharmendra/58db39d086b32e7dd208822480bbcacc to your computer and use it in GitHub Desktop.
1、
select i_item_id,
avg(cs_quantity) agg1,
avg(cs_list_price) agg2,
avg(cs_coupon_amt) agg3,
avg(cs_sales_price) agg4
from catalog_sales, customer_demographics, date_dim, item, promotion
where cs_sold_date_sk = d_date_sk and
cs_item_sk = i_item_sk and
cs_bill_cdemo_sk = cd_demo_sk and
cs_promo_sk = p_promo_sk and
cd_gender = 'M' and
cd_marital_status = 'D' and
cd_education_status = 'Advanced Degree' and
(p_channel_email = 'N' or p_channel_event = 'N') and
d_year = 1998
group by i_item_id
order by i_item_id
limit 10;
2、
select i_brand_id brand_id, i_brand brand,
sum(ss_ext_sales_price) ext_price
from date_dim, store_sales, item
where d_date_sk = ss_sold_date_sk
and ss_item_sk = i_item_sk
and i_manager_id=25
and d_moy=11
and d_year=2002
group by i_brand, i_brand_id
order by ext_price desc, i_brand_id
limit 10;
3、
select channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, SUM(ext_sales_price) sales_amt FROM (
SELECT 'store' as channel, 'ss_promo_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price
FROM store_sales, item, date_dim
WHERE ss_promo_sk IS NULL
AND ss_sold_date_sk=d_date_sk
AND ss_item_sk=i_item_sk
UNION ALL
SELECT 'web' as channel, 'ws_ship_customer_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price
FROM web_sales, item, date_dim
WHERE ws_ship_customer_sk IS NULL
AND ws_sold_date_sk=d_date_sk
AND ws_item_sk=i_item_sk
UNION ALL
SELECT 'catalog' as channel, 'cs_bill_hdemo_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price
FROM catalog_sales, item, date_dim
WHERE cs_bill_hdemo_sk IS NULL
AND cs_sold_date_sk=d_date_sk
AND cs_item_sk=i_item_sk) foo
GROUP BY channel, col_name, d_year, d_qoy, i_category
ORDER BY channel, col_name, d_year, d_qoy, i_category
limit 10;
4、
select avg(ss_quantity)
,avg(ss_ext_sales_price)
,avg(ss_ext_wholesale_cost)
,sum(ss_ext_wholesale_cost)
from store_sales
,store
,customer_demographics
,household_demographics
,customer_address
,date_dim
where s_store_sk = ss_store_sk
and ss_sold_date_sk = d_date_sk and d_year = 2001
and((ss_hdemo_sk=hd_demo_sk
and cd_demo_sk = ss_cdemo_sk
and cd_marital_status = 'U'
and cd_education_status = 'Secondary'
and ss_sales_price between 100.00 and 150.00
and hd_dep_count = 3
)or
(ss_hdemo_sk=hd_demo_sk
and cd_demo_sk = ss_cdemo_sk
and cd_marital_status = 'S'
and cd_education_status = 'Advanced Degree'
and ss_sales_price between 50.00 and 100.00
and hd_dep_count = 1
) or
(ss_hdemo_sk=hd_demo_sk
and cd_demo_sk = ss_cdemo_sk
and cd_marital_status = 'M'
and cd_education_status = 'College'
and ss_sales_price between 150.00 and 200.00
and hd_dep_count = 1
))
and((ss_addr_sk = ca_address_sk
and ca_country = 'United States'
and ca_state in ('AK', 'TX', 'WV')
and ss_net_profit between 100 and 200
) or
(ss_addr_sk = ca_address_sk
and ca_country = 'United States'
and ca_state in ('MT', 'NC', 'IN')
and ss_net_profit between 150 and 300
) or
(ss_addr_sk = ca_address_sk
and ca_country = 'United States'
and ca_state in ('MI', 'MO', 'KY')
and ss_net_profit between 50 and 250
))
limit 10;
5、
select c_last_name
,c_first_name
,c_salutation
,c_preferred_cust_flag
,ss_ticket_number
,cnt from
(select ss_ticket_number
,ss_customer_sk
,count(*) cnt
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 date_dim.d_dom between 1 and 2
and (household_demographics.hd_buy_potential = '>10000' or
household_demographics.hd_buy_potential = '5001-10000')
and household_demographics.hd_vehicle_count > 0
and case when household_demographics.hd_vehicle_count > 0 then
household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count else null end > 1
and date_dim.d_year in (1999,1999+1,1999+2)
and store.s_county in ('Williamson County','Williamson County','Williamson County','Williamson County')
group by ss_ticket_number,ss_customer_sk) dj,customer
where ss_customer_sk = c_customer_sk
and cnt between 1 and 5
order by cnt desc, c_last_name asc;
6 select
c_last_name,c_first_name,substr(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 = 1 or household_demographics.hd_vehicle_count > -1)
and date_dim.d_dow = 1
and date_dim.d_year in (2000,2000+1,2000+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,substr(s_city,1,30), profit
limit 10
;
7、
select sum (ss_quantity)
from store_sales, store, customer_demographics, customer_address, date_dim
where s_store_sk = ss_store_sk
and ss_sold_date_sk = d_date_sk and d_year = 2000
and
(
(
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'S'
and
cd_education_status = 'Advanced Degree'
and
ss_sales_price between 100.00 and 150.00
)
or
(
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'D'
and
cd_education_status = '4 yr Degree'
and
ss_sales_price between 50.00 and 100.00
)
or
(
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'U'
and
cd_education_status = 'Secondary'
and
ss_sales_price between 150.00 and 200.00
)
)
and
(
(
ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('MN', 'TN', 'IL')
and ss_net_profit between 0 and 2000
)
or
(ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('TX', 'OR', 'ID')
and ss_net_profit between 150 and 3000
)
or
(ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('MI', 'AL', 'RI')
and ss_net_profit between 50 and 25000
)
)
;
8 select i_item_id,
avg(cs_quantity) agg1,
avg(cs_list_price) agg2,
avg(cs_coupon_amt) agg3,
avg(cs_sales_price) agg4
from catalog_sales, customer_demographics, date_dim, item, promotion
where cs_sold_date_sk = d_date_sk and
cs_item_sk = i_item_sk and
cs_bill_cdemo_sk = cd_demo_sk and
cs_promo_sk = p_promo_sk and
cd_gender = 'M' and
cd_marital_status = 'D' and
cd_education_status = 'Advanced Degree' and
(p_channel_email = 'N' or p_channel_event = 'N') and
d_year = 1998
group by i_item_id
order by i_item_id
limit 10
;
9、
select distinct(i_product_name)
from item i1
where i_manufact_id between 761 and 761+40
and (select count(*) as item_cnt
from item
where (i_manufact = i1.i_manufact and
((i_category = 'Women' and
(i_color = 'midnight' or i_color = 'gainsboro') and
(i_units = 'Box' or i_units = 'Carton') and
(i_size = 'small' or i_size = 'large')
) or
(i_category = 'Women' and
(i_color = 'magenta' or i_color = 'chocolate') and
(i_units = 'Lb' or i_units = 'Bunch') and
(i_size = 'petite' or i_size = 'medium')
) or
(i_category = 'Men' and
(i_color = 'chartreuse' or i_color = 'chiffon') and
(i_units = 'Tbl' or i_units = 'Dozen') and
(i_size = 'economy' or i_size = 'extra large')
) or
(i_category = 'Men' and
(i_color = 'coral' or i_color = 'pale') and
(i_units = 'Ton' or i_units = 'Bundle') and
(i_size = 'small' or i_size = 'large')
))) or
(i_manufact = i1.i_manufact and
((i_category = 'Women' and
(i_color = 'burnished' or i_color = 'plum') and
(i_units = 'Oz' or i_units = 'Each') and
(i_size = 'small' or i_size = 'large')
) or
(i_category = 'Women' and
(i_color = 'misty' or i_color = 'mint') and
(i_units = 'Ounce' or i_units = 'Tsp') and
(i_size = 'petite' or i_size = 'medium')
) or
(i_category = 'Men' and
(i_color = 'dark' or i_color = 'olive') and
(i_units = 'Dram' or i_units = 'Gross') and
(i_size = 'economy' or i_size = 'extra large')
) or
(i_category = 'Men' and
(i_color = 'cornflower' or i_color = 'hot') and
(i_units = 'Gram' or i_units = 'N/A') and
(i_size = 'small' or i_size = 'large')
)))) > 0
order by i_product_name
limit 10
;
10、
select *
from(
select i_category, i_class, i_brand,
s_store_name, s_company_name,
d_moy,
sum(ss_sales_price) sum_sales,
avg(sum(ss_sales_price)) over
(partition by i_category, i_brand, s_store_name, s_company_name)
avg_monthly_sales
from item, store_sales, date_dim, store
where ss_item_sk = i_item_sk and
ss_sold_date_sk = d_date_sk and
ss_store_sk = s_store_sk and
d_year in (1998) and
((i_category in ('Electronics','Men','Books') and
i_class in ('cameras','shirts','entertainments')
)
or (i_category in ('Music','Women','Jewelry') and
i_class in ('classical','dresses','estate')
))
group by i_category, i_class, i_brand,
s_store_name, s_company_name, d_moy) tmp1
where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1
order by sum_sales - avg_monthly_sales, s_store_name
limit 10
;
11 select sum (ss_quantity)
from store_sales, store, customer_demographics, customer_address, date_dim
where s_store_sk = ss_store_sk
and ss_sold_date_sk = d_date_sk and d_year = 2000
and
(
(
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'S'
and
cd_education_status = 'Advanced Degree'
and
ss_sales_price between 100.00 and 150.00
)
or
(
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'D'
and
cd_education_status = '4 yr Degree'
and
ss_sales_price between 50.00 and 100.00
)
or
(
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'U'
and
cd_education_status = 'Secondary'
and
ss_sales_price between 150.00 and 200.00
)
)
and
(
(
ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('MN', 'TN', 'IL')
and ss_net_profit between 0 and 2000
)
or
(ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('TX', 'OR', 'ID')
and ss_net_profit between 150 and 3000
)
or
(ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('MI', 'AL', 'RI')
and ss_net_profit between 50 and 25000
)
)
;
12
select
s_store_name,
i_item_desc,
sc.revenue,
i_current_price,
i_wholesale_cost,
i_brand
from store, item,
(select ss_store_sk, avg(revenue) as ave
from
(select ss_store_sk, ss_item_sk,
sum(ss_sales_price) as revenue
from store_sales, date_dim
where ss_sold_date_sk = d_date_sk and d_month_seq between 1177 and 1177+11
group by ss_store_sk, ss_item_sk) sa
group by ss_store_sk) sb,
(select ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue
from store_sales, date_dim
where ss_sold_date_sk = d_date_sk and d_month_seq between 1177 and 1177+11
group by ss_store_sk, ss_item_sk) sc
where sb.ss_store_sk = sc.ss_store_sk and
sc.revenue <= 0.1 * sb.ave and
s_store_sk = sc.ss_store_sk and
i_item_sk = sc.ss_item_sk
order by s_store_name, i_item_desc
limit 10
;
select s_store_name,i_item_desc,sc.revenue,i_current_price,i_wholesale_cost,i_brand from store, item,(select ss_store_sk, avg(revenue) as ave from(select ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue from store_sales, date_dim where ss_sold_date_sk = d_date_sk and d_month_seq between 1177 and 1177+11 group by ss_store_sk, ss_item_sk) sa group by ss_store_sk) sb,(select ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue from store_sales, date_dim where ss_sold_date_sk = d_date_sk and d_month_seq between 1177 and 1177+11 group by ss_store_sk, ss_item_sk) sc where sb.ss_store_sk = sc.ss_store_sk and sc.revenue <= 0.1 * sb.ave and s_store_sk = sc.ss_store_sk and i_item_sk = sc.ss_item_sk order by s_store_name, i_item_desc limit 10;
13
select *
from
(select count(*) h8_30_to_9
from store_sales, household_demographics , time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 8
and time_dim.t_minute >= 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))
and store.s_store_name = 'ese') s1,
(select count(*) h9_to_9_30
from store_sales, household_demographics , time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 9
and time_dim.t_minute < 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))
and store.s_store_name = 'ese') s2,
(select count(*) h9_30_to_10
from store_sales, household_demographics , time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 9
and time_dim.t_minute >= 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))
and store.s_store_name = 'ese') s3,
(select count(*) h10_to_10_30
from store_sales, household_demographics , time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 10
and time_dim.t_minute < 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))
and store.s_store_name = 'ese') s4,
(select count(*) h10_30_to_11
from store_sales, household_demographics , time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 10
and time_dim.t_minute >= 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))
and store.s_store_name = 'ese') s5,
(select count(*) h11_to_11_30
from store_sales, household_demographics , time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 11
and time_dim.t_minute < 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))
and store.s_store_name = 'ese') s6,
(select count(*) h11_30_to_12
from store_sales, household_demographics , time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 11
and time_dim.t_minute >= 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))
and store.s_store_name = 'ese') s7,
(select count(*) h12_to_12_30
from store_sales, household_demographics , time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 12
and time_dim.t_minute < 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or
(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))
and store.s_store_name = 'ese') s8
;
select * from(select count(*) h8_30_to_9 from store_sales, household_demographics , time_dim, store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 8 and time_dim.t_minute >= 30 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2)) and store.s_store_name = 'ese') s1,(select count(*) h9_to_9_30 from store_sales, household_demographics , time_dim, store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 9 and time_dim.t_minute < 30 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))and store.s_store_name = 'ese') s2,(select count(*) h9_30_to_10 from store_sales, household_demographics , time_dim, store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 9 and time_dim.t_minute >= 30 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))and store.s_store_name = 'ese') s3,(select count(*) h10_to_10_30 from store_sales, household_demographics , time_dim, store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 10 and time_dim.t_minute < 30 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))and store.s_store_name = 'ese') s4,(select count(*) h10_30_to_11 from store_sales, household_demographics , time_dim, store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 10 and time_dim.t_minute >= 30 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))and store.s_store_name = 'ese') s5,(select count(*) h11_to_11_30 from store_sales, household_demographics , time_dim, store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 11 and time_dim.t_minute < 30 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2))and store.s_store_name = 'ese') s6,(select count(*) h11_30_to_12 from store_sales, household_demographics , time_dim, store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 11 and time_dim.t_minute >= 30 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or(household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2)) and store.s_store_name = 'ese') s7,(select count(*) h12_to_12_30 from store_sales, household_demographics , time_dim, store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 12 and time_dim.t_minute < 30 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2) or (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2)) and store.s_store_name = 'ese') s8;
14、
select count(*) from store_sales,household_demographics ,time_dim, store where ss_sold_time_sk
= time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk
and time_dim.t_hour = 8 and time_dim.t_minute >= 30 and household_demographics.hd_dep_count = 5 and
store.s_store_name = 'ese' order by count(*);
15
select
s_store_name
,s_company_id
,s_street_number
,s_street_name
,s_street_type
,s_suite_number
,s_city
,s_county
,s_state
,s_zip
,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end) as a
,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and
(sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end ) as b
,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and
(sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end) as c
,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and
(sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end) as d
,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0 end) as e
from
store_sales
,store_returns
,store
,date_dim d1
,date_dim d2
where
d2.d_year = 2000
and d2.d_moy = 8
and ss_ticket_number = sr_ticket_number
and ss_item_sk = sr_item_sk
and ss_sold_date_sk = d1.d_date_sk
and sr_returned_date_sk = d2.d_date_sk
and ss_customer_sk = sr_customer_sk
and ss_store_sk = s_store_sk
group by
s_store_name
,s_company_id
,s_street_number
,s_street_name
,s_street_type
,s_suite_number
,s_city
,s_county
,s_state
,s_zip
order by s_store_name
,s_company_id
,s_street_number
,s_street_name
,s_street_type
,s_suite_number
,s_city
,s_county
,s_state
,s_zip
limit 10
;
@RajenDharmendra
Copy link
Author

Multiple table queries used in bench-marking TPCDS on clickhouse
you can find the complete blog post @ https://aavin.dev/tpcds-benchmark-on-clickhouse-part1/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment