Помогите написать SQL запрос

Question

Нужно определить временные промежутки, в которые действовало несколько акций (promotion). Для каждого найденного периода вывести: дату начала, дату окончания, количество одновременно действующих акций.
Таблица promotions выглядит следующим образом:

promo_id NUMBER(6) NOT NULL,
promo_name VARCHAR2(30) NOT NULL,
promo_begin_date DATE NOT NULL,
promo_end_date DATE NOT NULL,
CONSTRAINT promotions_new_pk PRIMARY KEY (promo_id)

Условие не совсем понятное, но есть очень даже наглядный пример, предположим, что у нас есть акции:

prom1 - 01.01.2020 - 10.01.2020
prom2 - 01.01.2020 - 10.01.2020
prom3 - 03.01.2020 - 12.01.2020
prom4 - 15.01.2020 - 17.01.2020
prom5 - 15.01.2020 - 20.01.2020
prom6 - 15.01.2020 - 20.01.2020
prom7 - 21.01.2020 - 26.01.2020
prom8 - 22.01.2020 - 29.01.2020

В таком случае запрос должен выводить следующее:

01.01.2020 - 02.01.2020 - 2
03.01.2020 - 10.01.2020 - 3
15.01.2020 - 17.01.2020 - 3
18.01.2020 - 20.01.2020 - 2
22.01.2020 - 26.01.2020 - 2

Уточню, что я работаю в oracle11g и желательно, чтоб запрос смог там заработать.

Answer 1

Попробуйте данный запрос

SELECT promo_begin_date, promo_end_date, COUNT(promo_begin_date) FROM promotions GROUP BY promo_begin_date

Не уверен, что агрегатные функции в oracle будут записаны также, но думаю Вы сможете изменить синтакис

Answer 2

WITH 
cte1 (date_point, weight) AS (
  SELECT promo_begin_date, 1 FROM test
  UNION ALL
  SELECT promo_end_date, -1 FROM test
  ),
cte2 (date_point, next_date_point, sum_weight) AS (
  SELECT DISTINCT 
         date_point, 
         LEAD(date_point) OVER (ORDER BY date_point),
         SUM(weight) OVER (ORDER BY date_point) 
  FROM cte1
  )
SELECT date_point promo_begin_date,
       next_date_point promo_end_date, 
       sum_weight promo_amount
FROM cte2
WHERE next_date_point > date_point
  AND sum_weight > 1
ORDER BY promo_begin_date;

PROMO_BEGIN_DATE	PROMO_END_DATE	PROMO_AMOUNT
01-JAN-20	03-JAN-20	2
03-JAN-20	10-JAN-20	3
15-JAN-20	17-JAN-20	3
17-JAN-20	20-JAN-20	2
22-JAN-20	26-JAN-20	2

fiddle

Answer 3

Может проще выделить границы дат и по ним вывести:

-- доступные даты с сортировкой
WITH promo_dates AS (
  SELECT
    pr.promo_date,
    ROW_NUMBER() OVER (ORDER BY pr.promo_date) AS ordering
  FROM
    (
      SELECT
        p.promo_begin_date AS promo_date
      FROM promotion p
      UNION ALL
      SELECT
        p.promo_end_date AS promo_date
      FROM promotion p
    ) pr
  GROUP BY
    pr.promo_date
),
-- границы периода
promo_begin_end AS (
  SELECT 
    p.promo_date AS promo_begin_date,
    p2.promo_date AS promo_end_date
  FROM
    promo_dates p
    INNER JOIN promo_dates p2 ON p2.ordering = p.ordering + 1
)
-- подсчет входящих промоакций в период
SELECT
  be.promo_begin_date,
  be.promo_end_date,
  COUNT(*) AS promo_count
FROM
  promo_begin_end be
  INNER JOIN promotion p ON p.promo_begin_date <= be.promo_begin_date AND p.promo_end_date >= be.promo_end_date
GROUP BY
  be.promo_begin_date,
  be.promo_end_date;

результат выполнения:

PROMO_BEG	PROMO_END	PROMO_COUNT
26-JAN-20	29-JAN-20	1
22-JAN-20	26-JAN-20	2
21-JAN-20	22-JAN-20	1
17-JAN-20	20-JAN-20	2
15-JAN-20	17-JAN-20	3
10-JAN-20	12-JAN-20	1
03-JAN-20	10-JAN-20	3
01-JAN-20	03-JAN-20	2

БЛОГ НА HUSL

Помогите написать SQL запрос

Ответы (3 шт):