我有一个 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))