Получите отдельные имена, которые учетная запись имела до последнего дня в TERADATA или Oracle, без использования хранимых процедур, просто запросы
Мне нужно вычислить отдельные имена, которые имела учетная запись, где у меня есть дата каждого обновления, которое имела учетная запись:
Таким образом, для даты 01.01.2024 до этой даты у меня нет записей с учетной записью A с другим ИМЯ, но для записей в день 02.01.2024 для обеих записей у меня уже есть уникальная запись XOXO, поэтому на третий день у меня есть только два разных ИМЕНИ до этой даты.
Итак, результат моего запроса должен быть примерно таким:
Я уже пробовала PARTITION BY ACCOUNT ORDER BY NAME.
И это условие считает каждое имя уникальным, даже если оно повторяется как XOXO.
DENSE_RANK() OVER (Partition by ACCOUNT Order by NAME ASC) +
DENSE_RANK() OVER (Partition by ACCOUNT Order by NAME DESC) - 1 as COUNTP
Это условие всегда возвращало общее количество различных имен для каждой записи, даже для даты 2 февраля 2024 г., когда я ожидал, что будет учитываться только одна запись.
С уважением!
Но нет необходимости учитывать запись того дня, поскольку мне нужны только отдельные имена до этой даты, а не вычисление имени 06.01.2024. Итак, мы просто подсчитываем XOXO предыдущих дат и OXXO, XOOX и OOXO.
Ответы 3
Здесь вы можете использовать коррелированный подзапрос:
SELECT DISTINCT
t1.Date,
t1.ACCOUNT,
(SELECT COUNT(DISTINCT t2.NAME) FROM yourTable t2
WHERE t2.ACCOUNT = t1.ACCOUNT AND t2.Date < t1.Date) D_COUNT
FROM yourTable t1
ORDER BY
t1.ACCOUNT,
t1.Date;
Для первой даты он выдал 0, и это здорово, но для другой даты в результате он получил только 1.
Удалите t1.NAME из предложений SELECT и ORDER BY и добавьте GROUP BY t1.ACCOUNT, t1.DATE непосредственно перед ORDER BY. Это ближе к вашим результатам (когда объединяются несколько записей с одинаковой датой и аккаунтом.
Теперь я получаю только 0 или 1, но не такие результаты, как ожидалось. Спасибо!
Вы можете сделать это без коррелированного подзапроса, используя аналитическую функцию ROW_NUMBER для нумерации вхождений каждой пары имя/учетная запись, а затем, используя встроенное представление, суммировать первые появления каждой пары:
SELECT DISTINCT
dt,
account,
SUM(CASE rn WHEN 1 THEN 1 END) OVER (
PARTITION BY account
ORDER BY dt
RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '1' DAY PRECEDING
) AS count_distinct_name
FROM (
SELECT dt,
account,
ROW_NUMBER() OVER (PARTITION BY account, name ORDER BY dt) AS rn
FROM table_name
)
ORDER BY account, dt;
Что для примера данных:
CREATE TABLE table_name (Dt, ACCOUNT, NAME) AS
SELECT DATE '2024-01-01', 'A', 'XOXO' FROM DUAL UNION ALL
SELECT DATE '2024-01-02', 'A', 'XOXO' FROM DUAL UNION ALL
SELECT DATE '2024-01-02', 'A', 'OXXO' FROM DUAL UNION ALL
SELECT DATE '2024-01-04', 'A', 'XOOX' FROM DUAL UNION ALL
SELECT DATE '2024-01-05', 'A', 'OOXO' FROM DUAL UNION ALL
SELECT DATE '2024-01-06', 'A', 'XOXO' FROM DUAL;;
Выходы:
Спасибо! Это лучший подход, и он действительно дал мне ответ!
Ответ от MT0 может подойти Oracle. В Teradata нет RANGE BETWEEN, но аналогичный подход с ROWS BETWEEN будет работать:
SELECT
"date",
"ACCOUNT",
SUM(SUM(CASE rn WHEN 1 THEN 1 END)) OVER (
PARTITION BY "ACCOUNT"
ORDER BY "date"
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS count_distinct_NAME
FROM (
SELECT "date",
"ACCOUNT",
row_number() OVER (PARTITION BY "ACCOUNT", NAME ORDER BY "date") AS rn
FROM table_NAME
) occurrence_number
GROUP BY "ACCOUNT", "date"
ORDER BY "ACCOUNT", "date";
Поскольку предложение GROUP BY применяется перед аналитической функцией, поэтому в день имеется только одна строка, тогда да, ROWS BETWEEN работает так же, как RANGE BETWEEN (когда фильтрация с DISTINCT происходит после применения аналитической функции).
Почему число увеличивается до 4 06 января 2024 г., учитывая, что это имя
XOXOуже встречалось ранее?