• Home
• com.alibaba
• druid
• 1.2.21
• source code
• query70.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

tpcds.query70.sql Maven / Gradle / Ivy

select sum(ss_net_profit)                     as total_sum
     , s_state
     , s_county
     , grouping(s_state) + grouping(s_county) as lochierarchy
     , rank()                                    over (
 	partition by grouping(s_state)+grouping(s_county),
 	case when grouping(s_county) = 0 then s_state end 
 	order by sum(ss_net_profit) desc) as rank_within_parent
from store_sales
   , date_dim d1
   , store
where d1.d_month_seq between 1200 and 1200 + 11
  and d1.d_date_sk = ss_sold_date_sk
  and s_store_sk = ss_store_sk
  and s_state in
      (select s_state
       from (select s_state as s_state,
                    rank()     over ( partition by s_state order by sum(ss_net_profit) desc) as ranking
             from store_sales,
                  store,
                  date_dim
             where d_month_seq between 1200 and 1200 + 11
               and d_date_sk = ss_sold_date_sk
               and s_store_sk = ss_store_sk
             group by s_state) tmp1
       where ranking <= 5)
group by rollup (s_state, s_county)
order by lochierarchy desc
       , case when lochierarchy = 0 then s_state end
       , rank_within_parent limit 100