如何将一行添加到另一行

vaj7vani 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(615)

我有一组数据，如图所示：

我尝试使用lead函数将一月添加到二月，下图再次显示了这一点：

我以为它会像for循环一样不断地添加到下一行，但事实并非如此。
我请求帮助如何像for循环那样一次又一次地添加数据。我已经在下面粘贴了我使用的代码。

;WITH MonthActiveUsers AS (

        SELECT MonthLogin, COUNT(MonthLogin) AS ActiveUsers
        FROM #TEMPDistinctActiveUsersPerMonth DAUPM
        GROUP BY MonthLogin     
)  
SELECT MAU.MonthLogin, (LEAD(MAU.ActiveUsers,0) OVER (ORDER BY MAU.MonthLogin ASC) + LEAD(MAU.ActiveUsers,1) OVER (ORDER BY MAU.MonthLogin ASC)) AS [Count of Active Users]
FROM 
    (   SELECT CASE 
                    WHEN MonthLogin = 'January'
                    THEN '2020-01-01'
                    WHEN MonthLogin = 'February'
                    THEN '2020-02-01'
                    WHEN MonthLogin = 'March'
                    THEN '2020-03-01'
                    WHEN MonthLogin = 'April'
                    THEN '2020-04-01'
                    WHEN MonthLogin = 'May'
                    THEN '2020-05-01'
                    WHEN MonthLogin = 'June'
                    THEN '2020-06-01'
                    ELSE NULL
                END AS [MonthLogin]
                ,ActiveUsers
        FROM MonthActiveUsers MAU
        ) MAU

预期结果是：

January = 3313
February = 3349
March = 3398
April = 3421
May = 3437
June = 3444

sql window-functions sql-server Date count

来源：https://stackoverflow.com/questions/62312509/how-to-add-one-row-to-another

1条答案

按热度按时间

hfyxw5xn1#

我想你想要一个窗口总数：

WITH ...
SELECT 
    MAU.MonthLogin, 
    SUM(MAU.ActiveUsers) OVER (ORDER BY MAU.MonthLogin) [Count of Active Users]
FROM ...

注意，通过使用 CASE ，以及混合聚合和窗口函数：

SELECT 
    MonthLogin,
    COUNT(*) ActiveUsers,
    SUM(COUNT(*)) OVER(ORDER BY MonthLogin) [Count of Active Users]
FROM (
    SELECT 
        CASE MonthLogin
            WHEN'January'   THEN '2020-01-01'
            WHEN'February'  THEN '2020-02-01'
            WHEN'March'     THEN '2020-03-01'
            WHEN'April'     THEN '2020-04-01'
            WHEN'May'       THEN '2020-05-01'
            WHEN'June'      THEN '2020-06-01'
        END AS MonthLogin
    FROM #TEMPDistinctActiveUsersPerMonth
) t
GROUP BY MonthLogin
ORDER BY MonthLogin

赞(0）回复(0）举报 2021-07-29

我来回答

如何将一行添加到另一行

1条答案

相关问题

热门标签

最新问答