如何用配置表中的汇总视图更新表？
我有一个表A，它包含每个月帐户的相关数据，每个月可能有多个条目，下面的数据如代码片段所示。
期望转换表
我想要生成如下所示的表。其思想是，对于表A(配置)中的每个不同的account_id，希望生成一个汇总表，以便每个account_id在日历年度的每个月都有一个汇总条目，该月份的最新/最后一个account_id条目决定帐户是否处于活动状态。对于没有特定月份分录的帐户，ACTIVE列的值将为空。
如能就如何实现这一目标提供任何投入，将不胜感激。谢谢。
回答 2
Stack Overflow用户
发布于 2020-11-07 08:19:25
既然还没有人回答这个问题，我已经试过了。另一方面，我没有使用雪花，也没有找到一个地方，我可以用它来尝试我的代码(类似于db<>fiddle站点)，所以您可能需要修改它来适应雪花的约定。
然而，我试图使这个问题尽可能简单和笼统。如果有任何小问题，请将其转换为您需要的。
首先，我创建了基本数据集，以及一个数值表(称为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语句。
- 第一个CTE
All_Account_Months创建所需的所有行-每个Account_Id每月一行。 - 第二个CTE使用一个窗口函数来计算给定Account_ID和月份的最后一个Account_ID值。
- SELECT语句然后在它们之间进行左连接--从第一个CTE开始，然后向其中添加额外的列
Active。
为此，我用Server ( this )编写了它--我有一个运行的db<>fiddle here。但是，下面我尝试根据文档将其转换为雪花(例如，将列标识符更改为双引号，将格式更改为将月份名称转换为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";结果如下--和你的一样--除了我认为你在今年三月打错了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 NULLStack Overflow用户
发布于 2020-11-07 12:55:09
首先，您可以使用以下方法生成雪花中的日期：
select dateadd(month, seq4, '2020-01-01') as dte
from table (generator(rowcount => 12))因此，我们的想法是做以下工作：
- 生成您想要的日期。
- 交叉连接所有帐户以获得行。
- 做一些摆弄，以获得每个月的最后状态为每个帐户。
- 加入进来吧。
- 最后，如果一个月缺少一个状态，但之后有一个状态，则可以使用
lag(ignore nulls)来获得结果。
所以：
with months as (
select dateadd(month, seq4, '2020-01-01') as mon
from table (generator(rowcount => 12))
)
select a.account_id, m.mon,
coalesce(t.active,
lag(t.active ignore nulls) over (partition by a.account_id order by m.mon)
) as active
from (select distinct account_id from t) a cross join
months m left join
(select mon, account_id,
max(case when seqnum = 1 then active end) as active
from (select t.*, date_trunc('month', time) as mon,
row_number() over (partition by account_id, date_trunc('month', time) order by time desc) as seqnum
from t
) t
) t
on t.mon = m.mon and t.account_id = a.account_idhttps://stackoverflow.com/questions/64724539
复制相似问题
腾讯云开发者
