SQL查询 - 如何以“枢轴”中的“求和”功能中的重复天删除重复天数。条款

发布于 2025-01-30 15:22:54 字数 1806 浏览 1 评论 0原文

我有一个表ABS_DETAILS，可以提供以下数据 -

PERSON_NUMBER       ABS_DATE            ABS_TYPE_NAME               ABS_DAYS
1010            01-01-2022              PTO                             1
1010            01-01-2022              PTO                             1
1010            06-01-2022              PTO                             0.52
1010            02-02-2022              VACATION                        1
1010            03-02-2022              VACATION                        0.2
1010            01-12-2021              PTO                             1
1010            01-12-2021              PTO                             1
1010            02-12-2021              sick                            1                   
1010            30-12-2021              sick                            1
1010            30-01-2022              SICK                            1

输出看起来像 -

 PERSON_NUMBER          ABS_TYPE_NAME   1  2   3 4 5 6    7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31   
    1010                    PTO         4               0.52
    1010                    VACATION           1   0.2
    1010                    SICK                                           1                                                        2

使用查询 -

SELECT *
FROM
( 
    SELECT PERSON_NUMBER,
           EXTRACT(DAY FROM TO_DATE(ABS_DATE)) AS DAY_X,
           ABS_TYPE_NAME,
           ABS_DAYS
    FROM TABLE
    -- Add additional filter here which you want
 ) 
PIVOT(SUM(ABS_DAYS)
         FOR DAY_X IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31))

这主要为我提供了正确的输出。但是，我希望如果为2个日期说，例如01-01-2022具有PTO两次，那么该日期的ABS_DAYS的总和应该是1而不是2。

原文

I have a table abs_details that give data like follows -

PERSON_NUMBER       ABS_DATE            ABS_TYPE_NAME               ABS_DAYS
1010            01-01-2022              PTO                             1
1010            01-01-2022              PTO                             1
1010            06-01-2022              PTO                             0.52
1010            02-02-2022              VACATION                        1
1010            03-02-2022              VACATION                        0.2
1010            01-12-2021              PTO                             1
1010            01-12-2021              PTO                             1
1010            02-12-2021              sick                            1                   
1010            30-12-2021              sick                            1
1010            30-01-2022              SICK                            1

The output looks like -

 PERSON_NUMBER          ABS_TYPE_NAME   1  2   3 4 5 6    7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31   
    1010                    PTO         4               0.52
    1010                    VACATION           1   0.2
    1010                    SICK                                           1                                                        2

Using the query -

SELECT *
FROM
( 
    SELECT PERSON_NUMBER,
           EXTRACT(DAY FROM TO_DATE(ABS_DATE)) AS DAY_X,
           ABS_TYPE_NAME,
           ABS_DAYS
    FROM TABLE
    -- Add additional filter here which you want
 ) 
PIVOT(SUM(ABS_DAYS)
         FOR DAY_X IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31))

This is giving me the correct output mostly. But I want if for 2 dates say for example 01-01-2022 which has PTO twice, so the SUM of abs_Days for that date should be 1 and not 2. How can i tweak this inside the pivot function ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

尘曦 2025-02-06 15:22:54

也许我错了，但在我看来就像每月每天的abs_day的价值（1至31个，不知道为什么0是在这里）不应该是Greather，然后是1（就像1天是最大1天）。在这种情况下，您可能应该在Select子句中处理它...

SELECT 
    PERSON_NUMBER, ABS_TYPE_NAME, 
    CASE WHEN "0" > 1 THEN 1 ELSE "0" END "0", 
    CASE WHEN "1" > 1 THEN 1 ELSE "1" END "1", 
    CASE WHEN "2" > 1 THEN 1 ELSE "2" END "2",
    CASE WHEN "3" > 1 THEN 1 ELSE "3" END "3",
    CASE WHEN "4" > 1 THEN 1 ELSE "4" END "4",
    CASE WHEN "5" > 1 THEN 1 ELSE "5" END "5",
    CASE WHEN "6" > 1 THEN 1 ELSE "6" END "6",
--  ...
    CASE WHEN "30" > 1 THEN 1 ELSE "30" END "30",
    CASE WHEN "31" > 1 THEN 1 ELSE "31" END "31"
FROM
( 
    SELECT PERSON_NUMBER,
           EXTRACT(DAY FROM TO_DATE(ABS_DATE)) AS DAY_X,
           ABS_TYPE_NAME,
           ABS_DAYS
    FROM TABLE1
    -- Add additional filter here which you want
 ) 
PIVOT(Sum(ABS_DAYS)
         FOR DAY_X IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31))

Maybe I'm wrong, but it looks to me like the value of ABS_DAYS per every day in month (1 to 31, don't know why 0 is here) should not be greather then 1 (as like 1 day is 1 day max). In that case you should probably handle that in the Select clause...

SELECT 
    PERSON_NUMBER, ABS_TYPE_NAME, 
    CASE WHEN "0" > 1 THEN 1 ELSE "0" END "0", 
    CASE WHEN "1" > 1 THEN 1 ELSE "1" END "1", 
    CASE WHEN "2" > 1 THEN 1 ELSE "2" END "2",
    CASE WHEN "3" > 1 THEN 1 ELSE "3" END "3",
    CASE WHEN "4" > 1 THEN 1 ELSE "4" END "4",
    CASE WHEN "5" > 1 THEN 1 ELSE "5" END "5",
    CASE WHEN "6" > 1 THEN 1 ELSE "6" END "6",
--  ...
    CASE WHEN "30" > 1 THEN 1 ELSE "30" END "30",
    CASE WHEN "31" > 1 THEN 1 ELSE "31" END "31"
FROM
( 
    SELECT PERSON_NUMBER,
           EXTRACT(DAY FROM TO_DATE(ABS_DATE)) AS DAY_X,
           ABS_TYPE_NAME,
           ABS_DAYS
    FROM TABLE1
    -- Add additional filter here which you want
 ) 
PIVOT(Sum(ABS_DAYS)
         FOR DAY_X IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31))

回复收藏 0 原文

~没有更多了~