sql - SQL QUERY-如何在“PIVOT”子句中删除 sum 函数中的重复天数

我有一个 table 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 dates 来说例如 01-01-2022 有两次 PTO,那么该 date 的 abs_Days 的总和应该是 1 而不是 2。我如何在枢轴函数中调整它?

回答1

也许我错了,但在我看来,一个月中每天的 ABS_DAYS 的 value (1 到 31,不知道为什么这里是 0)不应该比 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))

相似文章