diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-21 11:54:28 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-21 11:54:28 +0000 |
| commit | e6918187568dbd01842d8d1d2c808ce16a894239 (patch) | |
| tree | 64f88b554b444a49f656b6c656111a145cbbaa28 /src/s3select/TPCDS/sample-queries-tpcds/query83.sql | |
| parent | Initial commit. (diff) | |
| download | ceph-e6918187568dbd01842d8d1d2c808ce16a894239.tar.xz ceph-e6918187568dbd01842d8d1d2c808ce16a894239.zip | |
Adding upstream version 18.2.2.upstream/18.2.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query83.sql')
| -rw-r--r-- | src/s3select/TPCDS/sample-queries-tpcds/query83.sql | 67 |
1 files changed, 67 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query83.sql b/src/s3select/TPCDS/sample-queries-tpcds/query83.sql new file mode 100644 index 000000000..d7bd1adec --- /dev/null +++ b/src/s3select/TPCDS/sample-queries-tpcds/query83.sql @@ -0,0 +1,67 @@ +-- start query 1 in stream 0 using template query83.tpl and seed 1930872976 +with sr_items as + (select i_item_id item_id, + sum(sr_return_quantity) sr_item_qty + from store_returns, + item, + date_dim + where sr_item_sk = i_item_sk + and d_date in + (select d_date + from date_dim + where d_week_seq in + (select d_week_seq + from date_dim + where d_date in (cast('2000-06-17' as date),cast('2000-08-22' as date),cast('2000-11-17' as date)))) + and sr_returned_date_sk = d_date_sk + group by i_item_id), + cr_items as + (select i_item_id item_id, + sum(cr_return_quantity) cr_item_qty + from catalog_returns, + item, + date_dim + where cr_item_sk = i_item_sk + and d_date in + (select d_date + from date_dim + where d_week_seq in + (select d_week_seq + from date_dim + where d_date in (cast('2000-06-17' as date),cast('2000-08-22' as date),cast('2000-11-17' as date)))) + and cr_returned_date_sk = d_date_sk + group by i_item_id), + wr_items as + (select i_item_id item_id, + sum(wr_return_quantity) wr_item_qty + from web_returns, + item, + date_dim + where wr_item_sk = i_item_sk + and d_date in + (select d_date + from date_dim + where d_week_seq in + (select d_week_seq + from date_dim + where d_date in (cast('2000-06-17' as date),cast('2000-08-22' as date),cast('2000-11-17' as date)))) + and wr_returned_date_sk = d_date_sk + group by i_item_id) + select sr_items.item_id + ,sr_item_qty + ,sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev + ,cr_item_qty + ,cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev + ,wr_item_qty + ,wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev + ,(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average + from sr_items + ,cr_items + ,wr_items + where sr_items.item_id=cr_items.item_id + and sr_items.item_id=wr_items.item_id + order by sr_items.item_id + ,sr_item_qty + limit 100; + +-- end query 1 in stream 0 using template query83.tpl |