update ceph source to reef 18.2.1

[ceph.git] / ceph / src / s3select / TPCDS / sample-queries-tpcds / query36.sql

diff --git a/ceph/src/s3select/TPCDS/sample-queries-tpcds/query36.sql b/ceph/src/s3select/TPCDS/sample-queries-tpcds/query36.sql
new file mode 100644 (file)
index 0000000..8456fc8
--- /dev/null
+++ b/ceph/src/s3select/TPCDS/sample-queries-tpcds/query36.sql
@@ -0,0 +1,30 @@
+-- start query 1 in stream 0 using template query36.tpl and seed 1544728811
+select  
+    sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
+   ,i_category
+   ,i_class
+   ,grouping(i_category)+grouping(i_class) as lochierarchy
+   ,rank() over (
+       partition by grouping(i_category)+grouping(i_class),
+       case when grouping(i_class) = 0 then i_category end 
+       order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
+ from
+    store_sales
+   ,date_dim       d1
+   ,item
+   ,store
+ where
+    d1.d_year = 1999 
+ and d1.d_date_sk = ss_sold_date_sk
+ and i_item_sk  = ss_item_sk 
+ and s_store_sk  = ss_store_sk
+ and s_state in ('IN','AL','MI','MN',
+                 'TN','LA','FL','NM')
+ group by rollup(i_category,i_class)
+ order by
+   lochierarchy desc
+  ,case when lochierarchy = 0 then i_category end
+  ,rank_within_parent
+  limit 100;
+
+-- end query 1 in stream 0 using template query36.tpl