sql - 对重叠类型进行分类 - Oracle

我已经能够使用 How do I find the total number of used days in a month? 来回答我们有多少天有猫和狗，这很有帮助，但我需要知道我们有多少天:

Cats only: 4
Dogs only: 5
Both: 6

先感谢您!

CREATE TABLE "ANIMALGUESTS"
(   "ID" NUMBER,
"GUESTNAME" VARCHAR2(20 BYTE),
"GUESTTYPE" VARCHAR2(20 BYTE),
"CHECKIN" DATE,
"CHECKOUT" DATE
);


Insert into ANIMALGUESTS (ID,GUESTNAME,GUESTTYPE,CHECKIN,CHECKOUT) values (1,'Tom','Cat',to_date('01-JAN-19','DD-MON-RR'),to_date('10-JAN-19','DD-MON-RR'));
Insert into ANIMALGUESTS (ID,GUESTNAME,GUESTTYPE,CHECKIN,CHECKOUT) values (2,'Spike','Dog',to_date('03-JAN-19','DD-MON-RR'),to_date('05-JAN-19','DD-MON-RR'));
Insert into ANIMALGUESTS (ID,GUESTNAME,GUESTTYPE,CHECKIN,CHECKOUT) values (3,'Spike','Dog',to_date('08-JAN-19','DD-MON-RR'),to_date('12-JAN-19','DD-MON-RR'));
Insert into ANIMALGUESTS (ID,GUESTNAME,GUESTTYPE,CHECKIN,CHECKOUT) values (4,'Cherie','Cat',to_date('07-JAN-19','DD-MON-RR'),to_date('09-JAN-19','DD-MON-RR'));
Insert into ANIMALGUESTS (ID,GUESTNAME,GUESTTYPE,CHECKIN,CHECKOUT) values (5,'Tyke','Dog',to_date('10-JAN-19','DD-MON-RR'),to_date('15-JAN-19','DD-MON-RR'));

最佳答案

使用条件聚合和内联日历表:

WITH cte AS (
  SELECT DATE '2019-01-01' + rownum -1 dt FROM DUAL CONNECT BY ROWNUM < 366
)
SELECT DISTINCT
  SUM(CASE WHEN COUNT(DISTINCT GUESTTYPE)=2 THEN 1 END) OVER() AS both,
  SUM(CASE WHEN COUNT(DISTINCT GUESTTYPE)=1 AND MIN(GUESTTYPE)='Cat' THEN 1 END) OVER() AS cats_only,
  SUM(CASE WHEN COUNT(DISTINCT GUESTTYPE)=1 AND MIN(GUESTTYPE)='Dog' THEN 1 END) OVER() AS dogs_only
FROM cte c
LEFT JOIN "ANIMALGUESTS" a ON c.dt BETWEEN a.CHECKIN AND a.CHECKOUT
GROUP BY dt;

db<>fiddle demo

关于sql - 对重叠类型进行分类 - Oracle，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57042865/