Блог

У меня есть SQL-запрос, который генерирует несколько строк за каждый час с информацией о рынке (по одной строке на рынок), например, накопленное количество уникальных клиентов на тот момент из таблицы, которая выглядит следующим образом, за которой следует запрос:

     select
        date_trunc('hour', current_timestamp) as "date_time"
        ,ct.country_code as market
        ,count(distinct ct.organization_id) acc_alltime_organizations
        ,count(distinct case when ct.transaction_date_time >= current_date - interval '30' day then ct.organization_id else null end) acc_1m_organizations
    from customer_transactions ct
    group by
            "date_time"
            ,market
 

Выше приведен пример таблицы, которую я создаю (на самом деле накоплений больше, но это не должно кардинально изменить настройку) для метки current_timestamp. Мне бы хотелось, чтобы эти строки относились не только к текущей отметке времени, но и к каждому часу, например, с тех пор 2016-01-01 10:00:00 и до сих пор. Это означает, что, например, я хочу знать, что за 2018-09-09 00:00:00 месяц, предшествующий этой дате, было 10000 организаций, которые когда-либо использовали продукт, и 1000 уникальных организаций, использовавших продукт.

Столбец со всеми часами, для которых мне нужны эти строки, может быть сгенерирован следующим образом:

 select count(*)
from generate_series(
    (select date_trunc('hour', min(ct.transaction_date_time))
                from customer_transactions ct 
    ),
    current_date,
    interval '1 hour'
) as t("date_time")
)
 

Желаемый результат выглядит примерно так, как показано в следующей таблице:

Поэтому мои мысли состояли в том, чтобы зациклиться на часах, сгенерированных с помощью приведенного выше фрагмента кода, и заменить current_date в первом фрагменте кода переменной даты, которую можно зациклить, и вставить в новую таблицу строку на рынок в час, но я застрял на том, как это реализовать.

Если есть другой метод, кроме зацикливания, который предпочтительнее, пожалуйста, также дайте мне знать :). Помощь в этом была бы очень признательна!

Если это ваш первоначальный запрос:

 select date_trunc('hour', current_timestamp) as date_time,
       ct.country_code as market,
       count(distinct ct.organization_id) as acc_alltime_organizations,
       count(distinct case when ct.transaction_date_time >= current_date - interval '30' day then ct.organization_id end) as acc_1m_organizations
from customer_transactions ct
group by ct.country_code;
 

Затем вы можете изменить его в течение нескольких часов, используя:

 select h.date_time,
       t.country_code as market,
       count(distinct ct.organization_id) as acc_alltime_organizations,
       count(distinct case when ct.transaction_date_time >= h.date_time - interval '30' day then ct.organization_id end) as acc_1m_organizations
from (select generate_series(min(ct.transaction_date_time), current_date, interval '1 hour') as date_time
      from customer_transactions ct
     ) h left join
     customer_transactions ct
     on ct.transaction_date_time <= h.date_time
group by h.date_time, ct.country_code
 

Возможно, существуют более эффективные способы расчета того, что вы хотите, но это отвечает на заданный вами вопрос. Возможно, вам захочется задать новый вопрос с образцами данных, желаемыми результатами и четким объяснением нужной вам логики.