Блог

Главная - Вопросы по программированию - Подзапрос SQL с использованием группы по элементу из основного запроса

Подзапрос SQL с использованием группы по элементу из основного запроса

#sql #postgresql #subquery #sql-subselect

#sql #postgresql #подзапрос

Вопрос:

У меня есть таблица с created меткой времени и id идентификатором.

Я могу получать количество уникальных id запросов в неделю с:

 SELECT date_trunc('week', created)::date AS week, count(distinct id) 
FROM my_table
GROUP BY week ORDER BY week;

Теперь я хочу иметь накопленное количество created по уникальным id данным за неделю, что-то вроде этого:

 SELECT date_trunc('week', created)::date AS week, count(distinct id),
       (SELECT count(distinct id)
        FROM my_table
        WHERE date_trunc('week', created)::date <= week) as acc
FROM my_table
GROUP BY week ORDER BY week;

Но это не работает, так как week недоступно во вложенном выборе ( ERROR: column "week" does not exist ).

Как мне это решить?

Я использую PostgreSQL

Ответ №1:

Используйте кумулятивную агрегацию. Но, я не думаю, что вам нужно distinct , поэтому:

 SELECT date_trunc('week', created)::date AS week, count(*) as cnt,
       SUM(COUNT(*)) OVER (ORDER BY MIN(created)) as running_cnt
FROM my_table
GROUP BY week
ORDER BY week;

В любом случае, как вы сформулировали проблему, вы можете изменить cnt на use count(distinct) . Ваш подзапрос не используется distinct вообще.

Комментарии:

1. Спасибо! Отсутствие distinct было ошибкой с моей стороны.

Ответ №2:

CTE или временная таблица должны решить вашу проблему. Вот пример использования CTE. 

  WITH abc AS (
     SELECT date_trunc('week', created)::date AS week, count(distinct id) as IDCount
     FROM my_table
     GROUP BY week ORDER BY week;
 )

SELECT abc.week, abc.IDcount,
       (SELECT count(*)
        FROM my_table
        WHERE date_trunc('week', created)::date <= adc.week) as acc
FROM abc
GROUP BY week ORDER BY abc.week;

Надеюсь, это поможет