f_item_scaling.sql

DROP TABLE IF EXISTS sandbox.f_test;
CREATE TABLE sandbox.f_test
(
    date_skey INTEGER ENCODE LZO,
    item_skey BIGINT ENCODE LZO,
    shop_skey INTEGER ENCODE LZO,
    field1    DECIMAL(12, 2) ENCODE LZO,
    field2    DECIMAL(12, 2) ENCODE LZO,
    field3    DECIMAL(12, 2) ENCODE LZO,
    field4    DECIMAL(12, 2) ENCODE LZO,
    field5    DECIMAL(12, 2) ENCODE LZO,
    field6    DECIMAL(12, 2) ENCODE LZO,
    field7    DECIMAL(12, 2) ENCODE LZO,
    field8    DECIMAL(12, 2) ENCODE LZO,
    field9    DECIMAL(12, 2) ENCODE LZO,
    field10   DECIMAL(12, 2) ENCODE LZO,
    field11   DECIMAL(12, 2) ENCODE LZO,
    field12   DECIMAL(12, 2) ENCODE LZO,
    field13   DECIMAL(12, 2) ENCODE LZO,
    field14   DECIMAL(12, 2) ENCODE LZO,
    field15   DECIMAL(12, 2) ENCODE LZO
)
    DISTKEY ( item_skey )
    SORTKEY (item_skey,date_skey,shop_skey);


DROP TABLE IF EXISTS  sandbox.date;
CREATE TABLE sandbox.date DISTSTYLE EVEN
                          SORTKEY (date_skey) AS
SELECT date_skey
  FROM star_analytical.d_calendar
 WHERE date_skey BETWEEN 20150101 AND 20221231;

DROP TABLE sandbox.items;
CREATE TABLE sandbox.items DISTKEY ( item_skey )
                           SORTKEY ( shop_skey) AS
SELECT item_skey, shop_skey
  FROM star_analytical.f_item_status
 WHERE period_date_skey = 20191027
 GROUP BY item_skey, shop_skey;

INSERT
  INTO sandbox.f_test
SELECT b.date_skey,
       a.item_skey,
       a.shop_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey
  FROM sandbox.items a
       CROSS JOIN sandbox.date b
 WHERE date_skey BETWEEN 20150101 AND 20151231;

INSERT
  INTO sandbox.f_test
SELECT b.date_skey,
       a.item_skey,
       a.shop_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey
  FROM sandbox.items a
       CROSS JOIN sandbox.date b
 WHERE date_skey BETWEEN 20160101 AND 20161231;

 INSERT
  INTO sandbox.f_test
SELECT b.date_skey,
       a.item_skey,
       a.shop_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey,
       random() * date_skey
  FROM sandbox.items a
       CROSS JOIN sandbox.date b
 WHERE date_skey BETWEEN 20170101 AND 20171231;
 
UNLOAD ('SELECT * from sandbox.f_test')
                        TO 's3://h24-bi-redshift-logs-production/ayushtest/'
                        iam_role 'arn:aws:iam::708984774556:role/BI-AllowS3'
                        MANIFEST
                        ALLOWOVERWRITE
                        CSV
                        ZSTD;
                

COPY sandbox.f_test
         FROM 's3://h24-bi-redshift-logs-production/ayushtest/manifest'
         IAM_ROLE 'arn:aws:iam::708984774556:role/BI-AllowS3'
         MANIFEST
         CSV
         ZSTD;                
                        
--Query 1 
SET enable_result_cache_for_session TO OFF;
SELECT avg(field1), avg(field2), date_skey, shop_skey, f.item_skey
  FROM sandbox.f_test f
 WHERE date_skey BETWEEN 20171201 AND 20171230
 GROUP BY date_skey, shop_skey, f.item_skey
 
 
--Query2 
SELECT sum(field1),date_skey, shop_skey
  FROM sandbox.f_test f
 WHERE item_skey = 11603522693
 GROUP BY date_skey,shop_skey
 
 --Query3
 SELECT item_skey,shop_skey,field1 from sandbox.f_test
where date_skey = 20170102
EXCEPT
SELECT item_skey,shop_skey,field1 from sandbox.f_test
where date_skey = 20170101

--Query4
SELECT item_skey,shop_skey,avg(field1),avg(field2) from sandbox.f_test
group by item_skey,shop_skey

--Query5 
SELECT f.item_skey, f.shop_skey, d.item_main_category, avg(field1), avg(field2)
  FROM sandbox.f_test f
       INNER JOIN star_analytical.d_item d
       ON f.item_skey = d.item_skey
 WHERE date_skey = 20170101
 GROUP BY f.item_skey, f.shop_skey, d.item_main_category