从Oracle表中选择小时作为列

Question

我正在使用Oracle数据库表，其结构如下:

TRANS_DATE      TRANS_HOUR_ENDING      TRANS_HOUR_SUFFIX        READING
1/1/2021        1                      1                        100
1/1/2021        2                      1                        105
...             ...                    ...                      ...
1/1/2021        24                     1                        115

TRANS_HOUR_SUFFIX仅用于跟踪日光节约时间结束的日子的小时读数(当有2小时具有相同的TRANS_HOUR值时)。这个专栏是这个数据库设计的祸根，但是我正尝试以某种方式选择这些数据。我们需要一个基于小时将这些数据列化的报告。因此，它的结构应该是这样的(最后一天显示DST结束的日期):

TRANS_DATE      HOUR_1    HOUR_2_1    HOUR_2_2   ...    HOUR_24
1/1/2021        100       105         0          ...    115
1/2/2021        112       108         0          ...    135
...             ...       ...         ...        ...    ...
11/7/2021       117       108         107        ...    121

我以前用PIVOT做过类似的事情，但是在这种情况下，我很难确定我应该做些什么来解释后缀。夏时制结束时，我们必须考虑到这一小时。我知道我们可以通过decode或case语句单独选择每个小时值来实现这一点，但这是一些混乱的代码。有更干净的方法吗?

Answer 1

您可以在数据透视for()和in()子句中包含多个源列，因此您可以这样做:

select *
from (
select trans_date,
trans_hour_ending,
trans_hour_suffix,
reading
from your_table
)
pivot (max(reading) for (trans_hour_ending, trans_hour_suffix)
in ((1, 1) as hour_1, (2, 1) as hour_2_1, (2, 2) as hour_2_2, (3, 1) as hour_3,
-- snip
(23, 1) as hour_23, (24, 1) as hour_24))
order by trans_date;

，其中每个小时有一个(24, 1)元组，与dst相关的小时有一个额外的(2, 2)元组。

如果您没有每个小时的行-您似乎没有从非常简短的样例数据中获得，至少对于非dst天的后缀2 -那么您将获得空结果，但可以将它们替换为零:

select trans_date,
coalesce(hour_1, 0) as hour_1,
coalesce(hour_2_1, 0) as hour_2_1,
coalesce(hour_2_2, 0) as hour_2_2,
coalesce(hour_3, 0) as hour_3,
-- snip
coalesce(hour_23, 0) as hour_23,
coalesce(hour_24, 0) as hour_24
from (
select trans_date,
trans_hour_ending,
trans_hour_suffix,
reading
from your_table
)
pivot (max(reading) for (trans_hour_ending, trans_hour_suffix)
in ((1, 1) as hour_1, (2, 1) as hour_2_1, (2, 2) as hour_2_2, (3, 1) as hour_3,
-- snip
(23, 1) as hour_23, (24, 1) as hour_24))
order by trans_date;

对于稍微展开的样本数据，它得到:

TRANS_DATE     HOUR_1   HOUR_2_1   HOUR_2_2     HOUR_3    HOUR_23    HOUR_24
---------- ---------- ---------- ---------- ---------- ---------- ----------
2021-01-01        100        105          0          0          0        115
2021-01-02        112        108          0          0          0        135
2021-11-07        117        108        107          0          0        121

当你必须在所有地方包括所有25列时，这有点冗长;但为了避免这种情况，你必须做一个动态枢轴。

Answer 2

就像我在我的评论中说的，如果你可以用额外的行来格式化它，我建议你只给额外的一小时留一行。每隔一天看起来都很正常。执行此操作的查询如下所示:

CREATE TABLE READINGS
(
TRANS_DATE DATE,
TRANS_HOUR INTEGER,
TRANS_SUFFIX INTEGER,
READING INTEGER
);
INSERT INTO readings
SELECT TO_DATE('01/01/2021', 'MM/DD/YYYY'), 1, 1, 100 FROM DUAL UNION ALL
SELECT TO_DATE('01/01/2021', 'MM/DD/YYYY'), 2, 1, 100 FROM DUAL UNION ALL
SELECT TO_DATE('11/07/2021', 'MM/DD/YYYY'), 1, 1, 200 FROM DUAL UNION ALL
SELECT TO_DATE('11/07/2021', 'MM/DD/YYYY'), 1, 2, 300 FROM DUAL UNION ALL
SELECT TO_DATE('11/07/2021', 'MM/DD/YYYY'), 2, 1, 500 FROM DUAL UNION ALL
SELECT TO_DATE('11/07/2021', 'MM/DD/YYYY'), 2, 2, 350 FROM DUAL;
SELECT TRANS_DATE||DECODE(MAX(TRANS_SUFFIX) OVER (PARTITION BY TRANS_DATE), 1, NULL, 2, ' - '||TRANS_SUFFIX) AS TRANS_DATE,
HOUR_1, HOUR_2, /*...*/ HOUR_24
FROM readings 
PIVOT (MAX(READING) FOR TRANS_HOUR IN (1 AS HOUR_1, 2 AS HOUR_2, /*...*/ 24 AS HOUR_24));

这将导致以下结果(对不起，我无法让dbfiddle工作):

<表类>TRANS_DATEHOUR_1HOUR_2HOUR_24tbody><<tr>01-JAN-21100100-07-NOV-21 - 1200500-07-NOV-21 - 2300350-