时间戳

首页 > 解决方案 > 无法按预期获得滚动不同的计数值

time - 无法按预期获得滚动不同的计数值

问题描述

我正在使用如下表格：

时期 县 四分之一 公司 产品
2020 年 1 月 1 日 德 2020 年 1 月 1 日 WKDM2 产品1
2020 年 1 月 1 日 德 2020 年 1 月 1 日 2GFSDG37 产品1
2020 年 1 月 2 日 德 2020 年 1 月 1 日 ORD56 产品2
2020 年 1 月 3 日 德 2020 年 1 月 1 日 GFDS 产品3
2020 年 1 月 3 日 德 2020 年 1 月 1 日 24GFDSGF2 产品1
2020 年 1 月 3 日 德 2020 年 1 月 1 日 2GFSDG37 产品3
2020 年 1 月 3 日 德 2020 年 1 月 1 日 24GSFD1 产品1
2020 年 1 月 4 日 德 2020 年 1 月 4 日 2GFSDG37 产品4
2020 年 1 月 4 日 德 2020 年 1 月 4 日 23GSFDG5 产品6
2020 年 1 月 4 日 德 2020 年 1 月 4 日 24GSFD1 产品1
2020 年 1 月 5 日 德 2020 年 1 月 4 日 23GSDF6 产品3
2020 年 1 月 6 日 德 2020 年 1 月 4 日 24GSFD1 产品8

我试图提取想要的数据，但这没有按预期工作，我对公司 Q 和产品 Q 的计数错误（要复制的代码）：

CREATE OR REPLACE TEMPORARY TABLE "TMP_TEST" (
    "Period"  TIMESTAMP,
    "Country"     VARCHAR,
    "Quarter"    TIMESTAMP,
    "Company"    VARCHAR,
    "Product"    VARCHAR
);

INSERT INTO "TMP_TEST" 
VALUES 
('01/01/2020','DE           ','01/01/2020     ','WKDM2    ','Product1'),
('01/01/2020','DE           ','01/01/2020     ','2GFSDG37  ','Product1'),
('01/02/2020','DE           ','01/01/2020     ','ORD56    ','Product2'),
('01/03/2020','DE           ','01/01/2020     ','GFDS     ','Product3'),
('01/03/2020','DE           ','01/01/2020     ','24GFDSGF2 ','Product1'),
('01/03/2020','DE           ','01/01/2020     ','2GFSDG37  ','Product3'),
('01/03/2020','DE           ','01/01/2020     ','24GSFD1   ','Product1'),
('01/04/2020','DE           ','01/04/2020     ','2GFSDG37  ','Product4'),
('01/04/2020','DE           ','01/04/2020     ','23GSFDG5  ','Product6'),
('01/04/2020','DE           ','01/04/2020     ','24GSFD1   ','Product1'),
('01/05/2020','DE           ','01/04/2020     ','23GSDF6   ','Product3'),
('01/06/2020','DE           ','01/04/2020     ','24GSFD1   ','Product8');

不工作查询：

SELECT t1."Period",t1."Country",t1."Quarter"
    ,COUNT(DISTINCT(t1."Company")) AS "Company",COUNT(DISTINCT(t1."Product")) AS "Product",
   (SELECT COUNT(DISTINCT("Company")) from "TMP_TEST" t2 where t1."Period" >= t2."Quarter" AND t1."Period" <= t2."Period" AND t1."Country" = t2."Country")  AS "Company Q",
   (SELECT COUNT(DISTINCT("Product")) from "TMP_TEST" t3 where t1."Period" >= t3."Quarter" AND t1."Period" <= t3."Period" AND t1."Country" = t3."Country")  AS "Product Q"
   FROM "TMP_TEST" t1
   group by 1,2,3
   ORDER BY 1

查询适用于 2020 年 1 月 3 日：

SELECT COUNT(DISTINCT("Company")) from "TMP_TEST" WHERE "Period" IN('01/01/2020','01/02/2020','01/03/2020' )

我想要的结果：

时期 县 四分之一 公司 产品 Q公司 产品Q
2020-01-01 德 2020-01-01 2 1 2 1
2020-01-02 德 2020-01-01 1 1 3 2
2020-01-03 德 2020-01-01 4 2 6 3
2020-01-04 德 2020-01-04 3 3 3 3
2020-01-05 德 2020-01-04 1 1 4 4
2020-01-06 德 2020-01-04 1 1 4 5

对于 2020-02-01 期间（包括 2020-02-01 和 2020-01-01 数据），我需要找到3 个独特的公司（WKDM2、2GFSDG37、ORD56）和2 个独特的产品（Product1、Product2）

对于 2020-03-01 期间（包括 2020-03-01、2020-02-01 和 2020-01-01 数据），我需要找到6 个独特的公司（WKDM2、2GFSDG37、ORD56、GFDS、24GFDSGF2、24GSFD1）和3 个独特的产品（Product1，Product2，Product3）。

你能告诉我我错了吗？

更新：

我创建了这个工作查询，这在性能方面是多余的。

WITH M AS (
  SELECT t3."Period",t3."Country",t3."Quarter"
    ,COUNT(DISTINCT(t3."Company")) AS "Company"
   FROM "TMP_TEST" t3
   group by 1,2,3
   ORDER BY 1
), Q AS (
  SELECT COUNT(DISTINCT(t1."Company")) AS "Company Q",t2."Period",t2."Country" from "TMP_TEST" t2,"TMP_TEST" t1 where t1."Period" >= t2."Quarter" AND t1."Period" <= t2."Period" AND t1."Country" = t2."Country"
GROUP BY t2."Period", t2."Country"
ORDER BY t2."Period"
), Y AS (
    SELECT COUNT(DISTINCT(t1."Company")) AS "Company Y",t2."Period",t2."Country" from "TMP_TEST" t2,"TMP_TEST" t1 where t1."Period" >= DATE_TRUNC('YEAR',t2."Quarter") AND t1."Period" <= t2."Period" AND t1."Country" = t2."Country"
    GROUP BY t2."Period", t2."Country"
    ORDER BY t2."Period"
)
SELECT M.*,Q."Company Q",Y."Company Y" from M,Q,Y WHERE M."Period" = Q."Period" AND M."Country" = Q."Country" AND M."Period" = Y."Period" AND M."Country" = Y."Country"

标签： timegroup-bysnowflake-cloud-data-platform

解决方案

这个解决方案感觉有点过头了，但它确实有效。

WITH comp_q AS (
    SELECT country, quarter, Period, company
        ,MIN(period) OVER (PARTITION BY country, quarter, company) as m_p
    FROM TMP_TEST
), prod_q AS (
    SELECT country, quarter, Period, Product
        ,MIN(period) OVER (PARTITION BY country, quarter, Product) as m_p
    FROM TMP_TEST
)

SELECT Period
    ,Country
    ,Quarter
    ,Company
    ,Product
    ,SUM(comp_q) OVER (PARTITION BY Country,Quarter order by period rows between unbounded preceding and current row) as "Company Q"
    ,SUM(prod_q) OVER (PARTITION BY Country,Quarter order by period rows between unbounded preceding and current row) as "Product Q"
FROM (
    SELECT t1.Period::date AS Period
        ,t1.Country
        ,t1.Quarter::DATE AS Quarter
        ,COUNT(DISTINCT(t1.Company)) AS Company
        ,COUNT(DISTINCT(t1.Product)) AS Product
        ,COUNT(DISTINCT q.company) as comp_q
        ,COUNT(DISTINCT p.Product) as prod_q
    FROM TMP_TEST AS t1
    LEFT JOIN comp_q AS q ON t1.Country = q.Country AND t1.Quarter = q.Quarter AND t1.period = q.m_p
    LEFT JOIN prod_q AS p ON t1.Country = p.Country AND t1.Quarter = p.Quarter AND t1.period = p.m_p
    GROUP BY 1,2,3
)
ORDER BY 1;

因此，这两个 CTE 在每个季度获得公司/产品存在的最早时期，然后加入该时期，这实际上允许一个不同的新公司/产品，然后可以在外层中求和。

感觉真的很恶心。

PERIOD  COUNTRY QUARTER COMPANY PRODUCT Company Q   Product Q
2020-01-01  DE              2020-01-01  2   1   2   1
2020-01-02  DE              2020-01-01  1   1   3   2
2020-01-03  DE              2020-01-01  4   2   6   3
2020-01-04  DE              2020-01-04  3   3   3   3
2020-01-05  DE              2020-01-04  1   1   4   4
2020-01-06  DE              2020-01-04  1   1   4   5

好的，所以有一种更清洁的方法：

SELECT Period
    ,Country
    ,Quarter
    ,COUNT(DISTINCT(Company)) AS Company
    ,COUNT(DISTINCT(Product)) AS Product
    ,MAX(Company_s) as company_Q
    ,MAX(Product_s) as Product_q
FROM (
    SELECT *
        ,sum(iff(r_c=1, 1,0)) OVER (PARTITION BY Country,Quarter order by period rows between unbounded preceding and current row) as Company_s
        ,sum(iff(r_p=1, 1,0)) OVER (PARTITION BY Country,Quarter order by period rows between unbounded preceding and current row) as Product_s
    FROM ( 
        SELECT *
            ,ROW_NUMBER() OVER(PARTITION BY Country,Quarter,company order by period ) as r_c
            ,ROW_NUMBER() OVER(PARTITION BY Country,Quarter,Product order by period ) as r_p
        FROM TMP_TEST
    )
)
GROUP BY 1,2,3
ORDER BY period;

这使用 ROW_NUMBER 为每个国家/季度为每个公司/产品分配一个计数器，然后我们可以只计算（IFF(r_c=1, 1,0)部件）的第一个实例，然后我们可以对这些值进行 WINDOW FRAME 累加。但是，这是在周期中，因此需要通过外层的 MAX 来解决：

推荐阅读