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

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

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，但它需要聚合，而我不想要聚合。您有任何解决方法吗？

数据：

  1. CREATE GLOBAL TEMPORARY TABLE my_gtt (id         NUMBER
  2.                                       ,dates      VARCHAR2(200)
  3.                                       ,is_active  NUMBER
  4.                                       ) ON COMMIT PRESERVE ROWS;
  5.     
  6. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 0);
  7. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 15:00', 0);
  8. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 0);
  9. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 1);
  10. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 10:00 - 13/07/2023 13:00', 1);
  11. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 1);
  12. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 13:00 - 13/07/2023 15:00', 1);
  13. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 10:00', 1);
  14. INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 0);
  15. INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 1);
  16. INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 1);
  17. 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:

  1. |ID|Dates|Is_Active|
  2. |----|----|----|
  3. |718844	|13/07/2023 18:00 - 13/07/2023 19:00|	0|
  4. |718842	|13/07/2023 08:00 - 13/07/2023 15:00|	0|
  5. |718844	|13/07/2023 06:00 - 13/07/2023 18:00|	0|
  6. |718842	|13/07/2023 06:00 - 13/07/2023 08:00|	0|
  7. |718842	|13/07/2023 15:00 - 13/07/2023 17:00|	0|
  8. |718844	|13/07/2023 18:00 - 13/07/2023 19:00|	1|
  9. |718844	|13/07/2023 06:00 - 13/07/2023 18:00|	1|
  10. |718842	|13/07/2023 08:00 - 13/07/2023 10:00|	1|
  11. |718842	|13/07/2023 13:00 - 13/07/2023 15:00|	1|
  12. |718842	|13/07/2023 10:00 - 13/07/2023 13:00|	1|
  13. |718842	|13/07/2023 06:00 - 13/07/2023 08:00|	1|
  14. |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.

  1. |ID|Inactive|Active|
  2. |----|----|----|
  3. |718844	|13/07/2023 18:00 - 13/07/2023 19:00|13/07/2023 18:00 - 13/07/2023 19:00|
  4. |718842	|13/07/2023 08:00 - 13/07/2023 15:00|13/07/2023 06:00 - 13/07/2023 18:00|
  5. |718844	|13/07/2023 06:00 - 13/07/2023 18:00|13/07/2023 08:00 - 13/07/2023 10:00|
  6. |718842	|13/07/2023 06:00 - 13/07/2023 08:00|13/07/2023 13:00 - 13/07/2023 15:00|
  7. |718842	|13/07/2023 15:00 - 13/07/2023 17:00|13/07/2023 10:00 - 13/07/2023 13:00|
  8. |718844	|                                   |13/07/2023 06:00 - 13/07/2023 08:00|
  9. |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:

  1.                  CREATE GLOBAL TEMPORARY TABLE my_gtt (id         NUMBER
  2.                                                       ,dates      VARCHAR2(200)
  3.                                                       ,is_active  NUMBER
  4.                                                       ) ON COMMIT PRESERVE ROWS;
  6. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 0);
  7. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 15:00', 0);
  8. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 0);
  9. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 1);
  10. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 10:00 - 13/07/2023 13:00', 1);
  11. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 1);
  12. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 13:00 - 13/07/2023 15:00', 1);
  13. INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 10:00', 1);
  14. INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 0);
  15. INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 1);
  16. INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 1);
  17. 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，以便永远不会有多个值被聚合：

  1. SELECT id,
  2.        inactive,
  3.        active
  4. FROM   (
  5.   SELECT t.*,
  6.          ROW_NUMBER() OVER (PARTITION BY id, is_active ORDER BY dates) AS rn
  7.   FROM   my_gtt t
  8. )
  9. 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:

  1. SELECT id,
  2.        inactive,
  3.        active
  4. FROM   (
  5.   SELECT t.*,
  6.          ROW_NUMBER() OVER (PARTITION BY id, is_active ORDER BY dates) AS rn
  7.   FROM   my_gtt t
  8. )
  9. 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
  • aggregate
  • oracle
  • pivot
  • sql
匿名

发表评论

匿名网友

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

确定