时间戳

首页 > 解决方案 > 雪花不确定查询时间比较

time - 雪花不确定查询时间比较

问题描述

我想计算与选定对象的上个月比较的减员。在这种情况下，我想知道从 2020-12-01 到 2021-01-01 丢失的客户。

SELECT  DISTINCT("Client Ref"),"Zone","Market Place","Report Period"
FROM    "REPORT_DB"."PBI"."Revenue" d
WHERE   "Report Period" ='2020-12-01' AND "Market Place" ='UK' AND "Client Ref" IS NOT NULL 
AND
NOT EXISTS
        (
        SELECT  "Client Ref"
        FROM    "REPORT_DB"."PBI"."Revenue" t
        WHERE   "Report Period" ='2021-01-01' AND "Market Place" ='UK' AND "Client Ref" IS NOT NULL AND d."Client Ref"=t."Client Ref"
        )

这是检索它的正确方法吗？

问候。

标签： timesnowflake-cloud-data-platform

解决方案

因此，通过添加带有一些虚拟数据的 CTE，并将列名更改为安全

WITH data AS (
    SELECT * FROM VALUES 
    (1,'a','UK','2020-12-01'),
    (1,'a','UK','2021-01-01'),
    (2,'a','UK','2020-12-01'),
    (3,'a','UK','2021-01-01')
    v( Client_Ref, zone, Market_Place, Report_Period)
)
SELECT DISTINCT d.Client_Ref,d.zone,d.Market_Place,d.Report_Period
FROM data AS d
WHERE d.Report_Period ='2020-12-01' AND d.Market_Place ='UK' AND d.Client_Ref IS NOT NULL 
AND
NOT EXISTS
        (
        SELECT  t.Client_Ref
        FROM    data t
        WHERE   t.Report_Period ='2021-01-01' AND t.Market_Place ='UK' AND t.Client_Ref IS NOT NULL AND d.Client_Ref=t.Client_Ref
        );

你为 SQL 工作的基础表单并返回：

CLIENT_REF  ZONE    MARKET_PLACE    REPORT_PERIOD
2           a       UK              2020-12-01

这是预期的结果。

此查询是关联子查询，Snowflake 对其支持有限。因此，虽然这有效，但当您更改查询时，它可能会遇到Unsupported subquery type cannot be evaluated错误，请参阅SO相关子查询问题。

LEFT JOINThw 基本查询可以通过使用then模式以不相关的形式编写WHERE x IS NULL：

WITH data AS (
    SELECT * FROM VALUES 
    (1,'a','UK','2020-12-01'),
    (1,'a','UK','2021-01-01'),
    (2,'a','UK','2020-12-01'),
    (3,'a','UK','2021-01-01')
    v( Client_Ref, zone, Market_Place, Report_Period)
)
SELECT DISTINCT d.Client_Ref,d.zone,d.Market_Place,d.Report_Period
FROM data AS d
LEFT JOIN data AS t
    ON t.Report_Period ='2021-01-01' AND t.Market_Place ='UK' AND d.Client_Ref=t.Client_Ref
WHERE d.Report_Period ='2020-12-01' AND d.Market_Place ='UK' AND d.Client_Ref IS NOT NULL 
AND t.Client_Ref IS NULL;

如果您的数据源有很多行不在目标结果范围内，则可以重写它以首先进行一些过滤，如下所示：

WITH data AS (
    SELECT * FROM VALUES 
    (1,'a','UK','2020-12-01'),
    (1,'a','UK','2021-01-01'),
    (2,'a','UK','2020-12-01'),
    (3,'a','UK','2021-01-01')
    v( Client_Ref, zone, Market_Place, Report_Period)
), wanted_data AS (
    SELECT DISTINCT Client_Ref, zone, Market_Place, Report_Period
    FROM data
    WHERE Report_Period BETWEEN '2020-12-01' AND '2021-01-01'
    AND Market_Place ='UK' AND Client_Ref IS NOT NULL
)
SELECT DISTINCT d.Client_Ref,d.zone,d.Market_Place,d.Report_Period
FROM wanted_data AS d
LEFT JOIN wanted_data AS t
    ON t.Report_Period ='2021-01-01'AND d.Client_Ref=t.Client_Ref
WHERE d.Report_Period ='2020-12-01' 
AND t.Client_Ref IS NULL;

但是对于我的生活，如果我像您一样命名列，我的 SQL 将不起作用"Client Ref"，因此我无法回答那部分，但这就是您构建 SQL 的方式。

推荐阅读