Oracle SQL – 不使用聚合函数进行PIVOT操作

huangapple go评论65阅读模式
英文:

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分析函数为每个idis_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

fiddle

英文:

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

fiddle

huangapple
  • 本文由 发表于 2023年7月13日 20:09:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/76679218.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定