英文:
Oracle SQL - PIVOT values without aggregate
问题
我的表格如下所示:
ID | 日期 | 是否活动 |
---|---|---|
718844 | 13/07/2023 18:00 - 13/07/2023 19:00 | 0 |
718842 | 13/07/2023 08:00 - 13/07/2023 15:00 | 0 |
718844 | 13/07/2023 06:00 - 13/07/2023 18:00 | 0 |
718842 | 13/07/2023 06:00 - 13/07/2023 08:00 | 0 |
718842 | 13/07/2023 15:00 - 13/07/2023 17:00 | 0 |
718844 | 13/07/2023 18:00 - 13/07/2023 19:00 | 1 |
718844 | 13/07/2023 06:00 - 13/07/2023 18:00 | 1 |
718842 | 13/07/2023 08:00 - 13/07/2023 10:00 | 1 |
718842 | 13/07/2023 13:00 - 13/07/2023 15:00 | 1 |
718842 | 13/07/2023 10:00 - 13/07/2023 13:00 | 1 |
718842 | 13/07/2023 06:00 - 13/07/2023 08:00 | 1 |
718842 | 13/07/2023 15:00 - 13/07/2023 17:00 | 1 |
我需要将我的数据转换为具有活动/非活动日期的不同列。
ID | 非活动 | 活动 |
---|---|---|
718844 | 13/07/2023 18:00 - 13/07/2023 19:00 | 13/07/2023 18:00 - 13/07/2023 19:00 |
718842 | 13/07/2023 08:00 - 13/07/2023 15:00 | 13/07/2023 06:00 - 13/07/2023 18:00 |
718844 | 13/07/2023 06:00 - 13/07/2023 18:00 | 13/07/2023 08:00 - 13/07/2023 10:00 |
718842 | 13/07/2023 06:00 - 13/07/2023 08:00 | 13/07/2023 13:00 - 13/07/2023 15:00 |
718842 | 13/07/2023 15:00 - 13/07/2023 17:00 | 13/07/2023 10:00 - 13/07/2023 13:00 |
718844 | 13/07/2023 06:00 - 13/07/2023 08:00 | |
718844 | 13/07/2023 15:00 - 13/07/2023 17:00 |
我尝试使用PIVOT,但它需要聚合,而我不想要聚合。您有任何解决方法吗?
数据:
CREATE GLOBAL TEMPORARY TABLE my_gtt (id NUMBER
,dates VARCHAR2(200)
,is_active NUMBER
) ON COMMIT PRESERVE ROWS;
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 15:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 10:00 - 13/07/2023 13:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 13:00 - 13/07/2023 15:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 10:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 0);
英文:
My table looks like this:
|ID|Dates|Is_Active|
|----|----|----|
|718844 |13/07/2023 18:00 - 13/07/2023 19:00| 0|
|718842 |13/07/2023 08:00 - 13/07/2023 15:00| 0|
|718844 |13/07/2023 06:00 - 13/07/2023 18:00| 0|
|718842 |13/07/2023 06:00 - 13/07/2023 08:00| 0|
|718842 |13/07/2023 15:00 - 13/07/2023 17:00| 0|
|718844 |13/07/2023 18:00 - 13/07/2023 19:00| 1|
|718844 |13/07/2023 06:00 - 13/07/2023 18:00| 1|
|718842 |13/07/2023 08:00 - 13/07/2023 10:00| 1|
|718842 |13/07/2023 13:00 - 13/07/2023 15:00| 1|
|718842 |13/07/2023 10:00 - 13/07/2023 13:00| 1|
|718842 |13/07/2023 06:00 - 13/07/2023 08:00| 1|
|718842 |13/07/2023 15:00 - 13/07/2023 17:00| 1|
and I need to transform my data to have active/inactive dates in separate columns.
|ID|Inactive|Active|
|----|----|----|
|718844 |13/07/2023 18:00 - 13/07/2023 19:00|13/07/2023 18:00 - 13/07/2023 19:00|
|718842 |13/07/2023 08:00 - 13/07/2023 15:00|13/07/2023 06:00 - 13/07/2023 18:00|
|718844 |13/07/2023 06:00 - 13/07/2023 18:00|13/07/2023 08:00 - 13/07/2023 10:00|
|718842 |13/07/2023 06:00 - 13/07/2023 08:00|13/07/2023 13:00 - 13/07/2023 15:00|
|718842 |13/07/2023 15:00 - 13/07/2023 17:00|13/07/2023 10:00 - 13/07/2023 13:00|
|718844 | |13/07/2023 06:00 - 13/07/2023 08:00|
|718844 | |13/07/2023 15:00 - 13/07/2023 17:00|
I was trying to use PIVOT but it requires aggregate which I don't want.
Do you have any idea how can I resolve this?
Data:
CREATE GLOBAL TEMPORARY TABLE my_gtt (id NUMBER
,dates VARCHAR2(200)
,is_active NUMBER
) ON COMMIT PRESERVE ROWS;
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 15:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 10:00 - 13/07/2023 13:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 13:00 - 13/07/2023 15:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 10:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 0);
答案1
得分: 1
使用ROW_NUMBER
分析函数为每个id
和is_active
值分配一个唯一值,然后使用该唯一值进行PIVOT
,以便永远不会有多个值被聚合:
SELECT id,
inactive,
active
FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY id, is_active ORDER BY dates) AS rn
FROM my_gtt t
)
PIVOT ( MAX(dates) FOR is_active IN (0 AS inactive, 1 AS active) )
对于您的示例数据,输出如下:
ID | INACTIVE | ACTIVE |
---|---|---|
718842 | 13/07/2023 06:00 - 13/07/2023 08:00 | 13/07/2023 06:00 - 13/07/2023 08:00 |
718842 | 13/07/2023 08:00 - 13/07/2023 15:00 | 13/07/2023 08:00 - 13/07/2023 10:00 |
718842 | 13/07/2023 15:00 - 13/07/2023 17:00 | 13/07/2023 10:00 - 13/07/2023 13:00 |
718842 | null | 13/07/2023 13:00 - 13/07/2023 15:00 |
718842 | null | 13/07/2023 15:00 - 13/07/2023 17:00 |
718844 | 13/07/2023 06:00 - 13/07/2023 18:00 | 13/07/2023 06:00 - 13/07/2023 18:00 |
718844 | 13/07/2023 18:00 - 13/07/2023 19:00 | 13/07/2023 18:00 - 13/07/2023 19:00 |
英文:
Use the ROW_NUMBER
analytic function to give each row, per id
and is_active
value, a unique value and then PIVOT
using that unique value so that you will never have more than one value being aggregated:
SELECT id,
inactive,
active
FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY id, is_active ORDER BY dates) AS rn
FROM my_gtt t
)
PIVOT ( MAX(dates) FOR is_active IN (0 AS inactive, 1 AS active) )
Which, for your sample data, outputs:
ID | INACTIVE | ACTIVE |
---|---|---|
718842 | 13/07/2023 06:00 - 13/07/2023 08:00 | 13/07/2023 06:00 - 13/07/2023 08:00 |
718842 | 13/07/2023 08:00 - 13/07/2023 15:00 | 13/07/2023 08:00 - 13/07/2023 10:00 |
718842 | 13/07/2023 15:00 - 13/07/2023 17:00 | 13/07/2023 10:00 - 13/07/2023 13:00 |
718842 | null | 13/07/2023 13:00 - 13/07/2023 15:00 |
718842 | null | 13/07/2023 15:00 - 13/07/2023 17:00 |
718844 | 13/07/2023 06:00 - 13/07/2023 18:00 | 13/07/2023 06:00 - 13/07/2023 18:00 |
718844 | 13/07/2023 18:00 - 13/07/2023 19:00 | 13/07/2023 18:00 - 13/07/2023 19:00 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论