Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / дубли записей при использовании оконных функций / 9 сообщений из 9, страница 1 из 1
20.04.2021, 18:13
    #40064254
scorpion235
scorpion235
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
Есть таблица расходов COSTS

Поля:
ID - идентификатор записи
REG_DATE - дата расхода
SUMM - сумма расхода

Мне нужно вывести агрегатную информацию в группировке по датам (за один день может быть несколько записей расходов):

SELECT REG_DATE,
COUNT(ID) OVER(PARTITION BY REG_DATE) COST_COUNT,
MIN(SUMM) OVER(PARTITION BY REG_DATE) MIN_SUMM,
MAX(SUMM) OVER(PARTITION BY REG_DATE) MAX_SUMM,
SUM(SUMM) OVER(PARTITION BY REG_DATE) SUMM
FROM COSTS
Такой запрос задублирует информацию для каждой записи расхода.
То есть, если есть 4 записи расходов за 2021-01-20, то запрос вернет 4 одинаковые записи за 2021-01-20

Дубли можно убрать вот таким запросом:

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY REG_DATE) ROW_NUM,
REG_DATE,
COUNT(ID) OVER(PARTITION BY REG_DATE) COST_COUNT,
MIN(SUMM) OVER(PARTITION BY REG_DATE) MIN_SUMM,
MAX(SUMM) OVER(PARTITION BY REG_DATE) MAX_SUMM,
SUM(SUMM) OVER(PARTITION BY REG_DATE) SUMM
FROM COSTS
)
WHERE ROW_NUM = 1
Но можно ли как-то обойтись без вложенных запросов?
DISTINCT не предлагать :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 18:20
    #40064255
Vlad F
Vlad F
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
scorpion235,

GROUP BY в первом запросе не забыл??
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 18:20
    #40064256
Шавлюк Евгений
Шавлюк Евгений
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
scorpion235,

Зачем здесь оконные функции?
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT REG_DATE,
       COUNT(ID) COST_COUNT,
       MIN(SUMM) MIN_SUMM,
       MAX(SUMM) MAX_SUMM,
       SUM(SUMM) SUMM
FROM COSTS
GROUP BY 1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 19:09
    #40064274
scorpion235
scorpion235
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
Шавлюк Евгений, не совсем презентабельный запрос написал. Сильно упростил

Полная версия такая:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY CL.REG_DATE) ROW_NUM,

       COALESCE ((
         SELECT FIRST(1) TRUE
           FROM COSTS C2
          WHERE C2.REG_DATE    = CL.REG_DATE
            AND C2.UNACCOUNTED = TRUE
       ), FALSE) UNACCOUNTED,

       COALESCE ((
         SELECT FIRST (1) TRUE
           FROM ERRANDS E
          WHERE CL.REG_DATE BETWEEN E.BDATE AND E.EDATE
       ), FALSE) ERRAND,

       COALESCE ((
         SELECT FIRST (1) TRUE
           FROM VACATIONS V
          WHERE CL.REG_DATE BETWEEN V.BDATE AND V.EDATE
       ), FALSE) VACATION,

       EXTRACT(YEARDAY FROM CL.REG_DATE)        PERIOD_NO,
       CL.REG_DATE                              PERIOD,
       COUNT(ID) OVER(PARTITION BY CL.REG_DATE) COST_COUNT,
       MIN(SUMM) OVER(PARTITION BY CL.REG_DATE) MIN_SUMM,
       MAX(SUMM) OVER(PARTITION BY CL.REG_DATE) MAX_SUMM,
       SUM(SUMM) OVER(PARTITION BY CL.REG_DATE) SUMM
  FROM PKB_DATE.CALENDAR('2010-08-16', '2021-04-20') CL
  LEFT JOIN COSTS C
         ON C.REG_DATE    = CL.REG_DATE
        AND C.UNACCOUNTED = FALSE
)
WHERE ROW_NUM = 1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 19:11
    #40064277
scorpion235
scorpion235
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
Работает быстро, но хочется избавиться от вложенных запросов
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 19:55
    #40064288
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
scorpion235,

оконные функции тут не нужны. Подзапросы с FIRST это вообще какая-то чепуха, можно заменить на EXISTS. Оставить обычный GROUP BY, а подзапросы вынести выше.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
WITH T AS (
  SELECT
       CL.REG_DATE  AS PERIOD,
       COUNT(ID) AS COST_COUNT,
       MIN(SUMM) AS MIN_SUMM,
       MAX(SUMM) AS MAX_SUMM,
       SUM(SUMM) AS SUMM
  FROM PKB_DATE.CALENDAR('2010-08-16', '2021-04-20') CL
  LEFT JOIN COSTS C
         ON C.REG_DATE    = CL.REG_DATE
        AND C.UNACCOUNTED = FALSE
  GROUP BY CL.REG_DATE)
SELECT
    EXISTS(
         SELECT *
           FROM COSTS C2
          WHERE C2.REG_DATE    = T.PERIOD
            AND C2.UNACCOUNTED = TRUE
       ) AS UNACCOUNTED,
    EXISTS(
         SELECT *
           FROM ERRANDS E
          WHERE T.PERIOD BETWEEN E.BDATE AND E.EDATE    
    ) AS ERRAND,
    EXISTS(
         SELECT *
           FROM VACATIONS V
          WHERE T.PERIOD BETWEEN V.BDATE AND V.EDATE    
    ) AS VACATION,
    EXTRACT(YEARDAY FROM T.PERIOD)  AS PERIOD_NO,
    T.PERIOD,
    T.COST_COUNT,
    T.MIN_SUMM,
    T.MAX_SUMM,
    T.SUMM
FROM T
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 20:20
    #40064293
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
scorpion235,

в 4.0 можно ещё извернуться через LEFT JOIN LATERAL, тогда можно вовсе без группировки обойтись.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 21:04
    #40064297
scorpion235
scorpion235
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
Симонов Денис,

Благодарю! Про LATERAL слышал
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 22:19
    #40064309
scorpion235
scorpion235
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
дубли записей при использовании оконных функций
Симонов Денис,

с LEFT JOIN LATERAL и без использования группировок производительность взлетела до небес
Execute time = 0ms :)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT CL.REG_DATE,
       C.COST_COUNT,
       C.SUMM,
       C.AVG_SUMM,
       C.MIN_SUMM,
       C.MAX_SUMM
  FROM PKB_DATE.CALENDAR('2010-08-16', '2021-04-20') CL
  LEFT JOIN LATERAL
  (
    SELECT COUNT(C.ID) COST_COUNT,
           SUM(C.SUMM) SUMM,
           AVG(C.SUMM) AVG_SUMM,
           MIN(C.SUMM) MIN_SUMM,
           MAX(C.SUMM) MAX_SUMM
      FROM COSTS C
     WHERE C.REG_DATE    = CL.REG_DATE
       AND C.UNACCOUNTED = FALSE
  ) C ON TRUE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / дубли записей при использовании оконных функций / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 500ms
Закрыть
start [/forum/topic.php?fid=40&mobile=1&tid=1560053]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 171ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 47ms
get tp. blocked users:
 2ms
others: 236ms
total:  500ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]