时间戳

首页 > 解决方案 > 如何使用配置表中的汇总视图更新表？

sql - 如何使用配置表中的汇总视图更新表？

问题描述

我有一个表 A，其中包含与每个月的帐户相关联的数据，每个月可能有多个条目，其中包含以下数据，如代码段所示。

在此处输入图像描述

预期的转换表

我想生成如下所示的表格，如下面的片段所示。想法是，对于表 A（配置）中的每个不同的 account_id，想要生成一个汇总表，以便每个 account_id 将在日历年中的每个月都有一个汇总条目，该月的 account_id 的最新/最后一个条目决定是否帐户是否处于活动状态。对于没有特定月份条目的帐户，ACTIVE 列的值将为空。

在此处输入图像描述

任何有关如何实现这一点的意见将不胜感激。谢谢你。

标签： sqlsnowflake-cloud-data-platform

解决方案

鉴于还没有人回答这个问题，我试了一下。另一方面，我没有使用 Snowflake，也找不到可以尝试我的代码的地方（类似于 db<>fiddle 站点），因此您可能需要修改它以适应 Snowflake 的约定。

然而，我试图让这个尽可能简单和通用。如果有任何小问题，请将其转换为您需要的。

首先，我创建了基本数据集，以及一个数值表（称为Num），其值为 0 到 11。您可以使用任何其他方式来生成数字；我只是将其用作创建月份列表的一种相当通用的方法。

CREATE TABLE TableA ("Time" datetime, Account_Id varchar(3), Active varchar(1))
INSERT INTO TableA ("Time", Account_Id, Active) VALUES
('2020-02-25 00:00:30', '111', 'T'),
('2020-02-15 00:10:30', '112', 'T'),
('2020-03-17 10:00:30', '111', 'F'),
('2020-04-09 01:00:24', '111', 'T'),
('2020-05-18 01:00:22', '112', 'F'),
('2020-01-01 11:11:11', '111', 'T'),
('2020-02-28 10:00:00', '111', 'F'),
('2020-05-28 01:00:22', '112', 'F');

CREATE TABLE Nums (n int);
INSERT INTO Nums (n) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

下一步涉及三个步骤：2 个 CTE，以及将它们组合在一起的 SELECT 语句。

  • 第一个 CTEAll_Account_Months创建您需要的所有行 - 每个 Account_Id 每月一行。
  • 第二个 CTE 使用窗口函数Active计算给定 Account_ID 和月份的最后一个值。
  • 然后 SELECT 语句在它们之间执行 LEFT JOIN - 从第一个 CTE 开始，然后向其中添加额外的列Active。

为此，我在 SQL Server (t-sql) 中编写了它——我在运行时有一个db<>fiddle。但是，在下面，我尝试根据文档将其转换为 Snowflake（例如，将列标识符更改为双引号，将 FORMAT 更改为将月份名称更改为 TO_VARCHAR）。不过很抱歉 - 我没有地方可以测试它。

DECLARE @StartDate datetime = '2020-01-01 00:00:00';

WITH All_Account_Months AS
    (SELECT A.Account_ID,
            TO_VARCHAR(DATEADD(month, N.n, @StartDate), 'MMMM yyyy') AS "Month"
     FROM   Nums N
            CROSS JOIN
                (SELECT DISTINCT Account_ID
                    FROM TableA
                ) A
    ),
Latest_Active_Values AS
    (SELECT * 
     FROM   (SELECT *,
                TO_VARCHAR(Asub."Time", 'MMMM yyyy') AS "Month",
                ROW_NUMBER() OVER (PARTITION BY Asub.Account_ID, YEAR(Asub."Time"), MONTH(Asub."Time") ORDER BY Asub."Time" DESC) AS rn
            FROM TableA Asub
            ) AS A
     WHERE  A.rn = 1
    )
SELECT  AAM.Account_ID, AAM.Month, LAV.Active
FROM    All_Account_Months AAM
        LEFT OUTER JOIN Latest_Active_values LAV
            ON AAM.Account_Id = LAV.Account_Id
            AND AAM."Month" = LAV."Month";

结果如下 - 和你的一样，除了我认为你在 March 上打错了 111 - 它应该是 F 而不是 T？

Account_ID  Month           Active
111         January 2020    T
111         February 2020   F
111         March 2020      F
111         April 2020      T
111         May 2020        NULL
111         June 2020       NULL
111         July 2020       NULL
111         August 2020     NULL
111         September 2020  NULL
111         October 2020    NULL
111         November 2020   NULL
111         December 2020   NULL
112         January 2020    NULL
112         February 2020   T
112         March 2020      NULL
112         April 2020      NULL
112         May 2020        F
112         June 2020       NULL
112         July 2020       NULL
112         August 2020     NULL
112         September 2020  NULL
112         October 2020    NULL
112         November 2020   NULL
112         December 2020   NULL

推荐阅读