时间戳

首页 > 解决方案 > 雪花云数据库查询以查找新 SKU 和已删除 SKU 的计数

sql - 雪花云数据库查询以查找新 SKU 和已删除 SKU 的计数

问题描述

我在表格中有以下列，并希望通过比较前一天来显示新 sku 和已删除 sku 的商店日期明智计数。

SHOP_Date       SKU     
01-13-2020       1
01-13-2020       2
01-14-2020       1
01-14-2020       3
01-15-2020       4
01-15-2020       2

我的输出应该如下所示

shop date        #New product         #Remove product
01-13-2020           2                     0
01-14-2020           1                     1
01-15-2020           2                     2

标签： sqlsnowflake-cloud-data-platform

解决方案

这是我的解决方案逐步实现的结果，使用 WITH 子句，因此您需要选择完整的文本运行，这在 Oracle 中按原样工作，如果您想在 Snowflake 中运行，只需删除“from dual”。

WITH t 
 AS (SELECT 13 AS shop_date, 
            1  AS SKU 
     FROM   dual 
     UNION ALL 
     SELECT 13 AS shop_date, 
            2  AS SKU 
     FROM   dual 
     UNION ALL 
     SELECT 14 AS shop_date, 
            1  AS SKU 
     FROM   dual 
     UNION ALL 
     SELECT 14 AS shop_date, 
            3  AS SKU 
     FROM   dual 
     UNION ALL 
     SELECT 15 AS shop_date, 
            4  AS SKU 
     FROM   dual 
     UNION ALL 
     SELECT 15 AS shop_date, 
            2  AS SKU 
     FROM   dual), 
 date_rel 
 AS (SELECT shop_date                AS C_Day, 
            Lag(shop_date) 
              over ( 
                ORDER BY shop_date ) AS P_Day 
     FROM   (SELECT DISTINCT shop_date 
             FROM   t)), 
 c_mns_p 
 AS (SELECT shop_date AS d1, 
            sku 
     FROM   t 
     MINUS 
     (SELECT date_rel.c_day d1, 
             t1.sku 
      FROM   date_rel, 
             t t1 
      WHERE  date_rel.p_day = t1.shop_date)), 
 p_mns_c 
 AS ((SELECT date_rel.c_day d1, 
             t1.sku 
      FROM   date_rel, 
             t t1 
      WHERE  date_rel.p_day = t1.shop_date) 
     MINUS 
     SELECT shop_date AS d1, 
            sku 
     FROM   t), 
 c_mns_p_cnt 
 AS (SELECT d1, 
            Count(sku) cnt 
     FROM   c_mns_p 
     GROUP  BY d1), 
 p_mns_c_cnt 
 AS (SELECT d1, 
            Count(sku) cnt 
     FROM   p_mns_c 
     GROUP  BY d1), 
 result 
 AS (SELECT d_rel.c_day             AS Shop_Date, 
            c_mns_p_cnt.cnt         AS New_sku_Cnt, 
            Nvl(p_mns_c_cnt.cnt, 0) AS removed_sku_cnt 
     FROM   date_rel d_rel, 
            c_mns_p_cnt, 
            p_mns_c_cnt 
     WHERE  d_rel.c_day = c_mns_p_cnt.d1 
            AND d_rel.c_day = p_mns_c_cnt.d1(+)) 
SELECT * FROM   result ORDER  BY 1;

推荐阅读