#sql #oracle
#sql #Oracle
Вопрос:
Я много раз просматривал форум, но не мог найти решения для моей ситуации. Я работаю с базой данных Oracle.
У меня есть таблица со всеми номерами заказов и номерами клиентов по дням. Это выглядит следующим образом:
Day | Customer Nbr | Order Nbr
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....
Теперь мне нужен SQL-запрос, который выдает мне таблицу по дням и Nbr клиентов и подсчитывает количество уникальных номеров заказов за последние 365 дней, начиная со столбца 1.
В приведенном выше примере результирующая таблица должна выглядеть следующим образом:
Day | Customer Nbr | Order Cnt
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1
Комментарии:
1. не могли бы вы, пожалуйста, добавить больший набор данных для данных и вывода?
2. Как это выводит «обратный отсчет»? Не должна ли первая строка содержать сегодняшнюю дату в порядке убывания или сегодня — 1 год в порядке возрастания?
3. Мне очень жаль, но я не понимаю вашего вопроса. Я пытаюсь объяснить другими словами. Исходная таблица содержит все номера заказов и клиентов за день. В результирующей таблице я хочу видеть за каждый прошедший день, сколько заказов сделал каждый клиент. Но только в течение определенного периода времени, который находится между днем, на который мы смотрим, и (днем, на который мы смотрим, минус 364 дня). Итак, допустим, клиент A сделал 5 заказов в 2018 году.
4. Мне очень жаль, но я не понимаю вашего вопроса. Я пытаюсь объяснить другими словами. Итак, допустим, клиент A сделал 5 заказов в 2018 году. Мне нужна таблица на каждый день в 2019 году (в широте, скажем, начиная с 2019-01-01). В первой строке указано количество заказов, сделанных клиентом в период с 2018-01-02 по 2019-01-01 (что равно 5). Вторая строка — 2019-01-02 и показывает количество заказов, сделанных клиентом в период с 2018-01-03 по 2019-01-02. Третья строка — 2019-01-03 и показывает количество заказов, сделанных клиентом в период с 2018-01-04 по 2019-01-03
Ответ №1:
Один из методов заключается в генерации значений для всех интересующих дней для каждого клиента, а затем использовании соответствующего подзапроса:
with dates as (
select date '2019-01-01' rownum as dte from dual
connect by date '2019-01-01' rownum < sysdate
)
select d.dte, t.customer_nbr,
(select count(*)
from t t2
where t2.customer_nbr = t.customer_nbr and
t2.day <= t.dte and
t2.date > t.dte - 365
) as order_cnt
from dates d cross join
(select distinct customer_nbr from t) ;
Ответ №2:
Редактировать:
Я только что видел, как вы разъясняете вопрос, который, как я понял, означает: за каждый день в прошлом году покажите, сколько заказов было для каждого клиента между этой датой и 1 годом ранее. Сейчас работаю над ответом…
Обновленный ответ:
Для каждого клиента мы подсчитываем количество записей между днем заказа и 365 днями до него…
WITH yourTable AS
(
SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
UNION ALL
SELECT SYSDATE - 2, 'Alex' FROM DUAL
UNION ALL
SELECT SYSDATE - 366, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 400, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 500, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Joe'FROM DUAL
UNION ALL
SELECT SYSDATE - 300, 'Chris'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Chris'FROM DUAL
)
SELECT Day, CustomerNbr, OrdersLast365Days
FROM yourTable t
OUTER APPLY
(
SELECT COUNT(1) OrdersLast365Days
FROM yourTable t2
WHERE t.CustomerNbr = t2.CustomerNbr
AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
AND TRUNC(t2.Day) <= TRUNC(t.Day)
)
ORDER BY t.Day DESC, t.CustomerNbr;
Если вы хотите сообщить только о тех днях, на которые у вас есть заказы, тогда простого WHERE предложения должно быть достаточно:
SELECT Day, CustomerNbr, COUNT(1) OrderCount
FROM <yourTable>
WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
GROUP BY Day, CustomerNbr
ORDER BY Day Desc;
Если вы хотите создавать отчеты за каждый день, вам нужно сначала сгенерировать их. Это может быть сделано с помощью рекурсивного CTE, который затем вы присоединяете к своей таблице:
WITH last365Days AS
(
SELECT TRUNC (SYSDATE - ROWNUM 1) dt
FROM DUAL CONNECT BY ROWNUM < 365
)
SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
FROM last365Days d
LEFT OUTER JOIN <yourTable> t
ON d.Day = TRUNC(t.Day)
GROUP BY d.Day, t.CustomerNbr
ORDER BY d.Day Desc;
Комментарии:
1. Спасибо за ваш ответ. Я не все понимаю и просто попробую. Что такое DUAL? Я часто получаю «неподдерживаемое сглаживание столбцов». Вы знаете, что это может означать?
2. Нет проблем. Я думаю, что «Неподдерживаемое сглаживание столбцов» произошло из-за того, что я использовал CTE (с x (col1, col2) КАК ….), который не работал до Oracle 11.2 — я предполагаю, что вы используете более старую версию? DUAL — это таблица-заполнитель, поэтому вы можете выбирать что-либо без использования реальной таблицы (ВЫБЕРИТЕ ФИКТИВНУЮ ИЗ DUAL), просто возвращает ‘x’. Хотя я в основном использую его для применения логики к столбцам в CROSS / OUTER APPLY или CTE.
3. Я обновил CTE, чтобы использовать старый способ псевдонимирования столбцов и более старую инструкцию CONNECT BY для генерации 365 дней
Ответ №3:
Я бы, вероятно, сделал это с помощью аналитической функции and. В вашем предложении windowing вы можете указать количество строк до или диапазон. В этом случае я буду использовать диапазон.
Это даст вам для каждого клиента за каждый день количество заказов в течение одного скользящего года до отображаемой даты
WITH DATES AS (
SELECT * FROM
(SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
CROSS JOIN
(SELECT DISTINCT CUST_ID FROM MY_TABLE))
SELECT DISTINCT
DATES.DAY,
DATES.CUST_ID,
COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
FROM
DATES
LEFT JOIN
MY_TABLE
ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
ORDER BY DATES.CUST_ID,DATES.DAY;