Блог

Главная - Вопросы по программированию - Фиксированный диапазон временных меток для каждого uuid в SQL

Фиксированный диапазон временных меток для каждого uuid в SQL

#postgresql

#postgresql

Вопрос:

Я хотел бы сгенерировать таблицу с временными метками данных за последние n недель (в данном случае n = 3) и всеми данными, даже если они равны нулю.

Я использую следующие фрагменты кода

    with raw_weekly_data as (SELECT
   distinct d.uuid,
   date_trunc('week',a.start_timestamp) as tstamp,
   avg(price) as price
   FROM
   a join d on a.uuid = d.uuid
   where start_timestamp between date_trunc('week',now()) -           interval '3 week' and date_trunc('week',now())
   group by 1,2,3
   order by 1)

   ,tstamp as (SELECT
   distinct tstamp
   FROM
   raw_weekly_data
   )

   SELECT
   t.tstamp,
   r.*
   from raw_weekly_data r right join tstamp t on r.tstamp =       t.tstamp
   order by uuid

Я хотел бы иметь что-то подобное:

 week  |  uuid  | price
w1    |  1     | 10
w2    |  1     | 2
w3    |  1     |
w1    |  2     | 20
w2    |  2     |
w3    |  2     |
w1    |  3     | 10
w2    |  3     | 10
w3    |  3     | 20

Но вместо этого не отображаются все нулевые результаты. Каков наилучший подход здесь?

 week  |  uuid  | price
w1    |  1     | 10
w2    |  1     | 2
w1    |  2     | 20
w1    |  3     | 10
w2    |  3     | 10
w3    |  3     | 20

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

1. Возможно, вам нужно a left join d . Чтобы знать это наверняка и предоставить вам рабочее решение, мы хотели бы, чтобы вы добавили образцы данных для таблиц a и d

2. Ваш вопрос должен начинаться с вашей версии Postgres и определений таблиц ( CREATE TABLE инструкция). Также помогли бы некоторые примеры данных.

Ответ №1:

Сформируйте декартово произведение всех недель на UUID, затем LEFT JOIN на фактическое среднее значение, цены за (week, uuid) . Нравится:

 SELECT *
FROM   generate_series (date_trunc('week', now() - interval '3 week')
                                         , now() - interval '1 week'
                      , interval '1 week') tstamp
CROSS  JOIN (SELECT DISTINCT uuid FROM a) a
LEFT   JOIN (
   SELECT d.uuid
        , date_trunc('week', a.start_timestamp) AS tstamp
        , avg(price) AS price  -- d.price?
   FROM   a
   JOIN   d USING (uuid)
   WHERE  a.start_timestamp >= date_trunc('week',now()) - interval '3 week'
   AND    a.start_timestamp <  date_trunc('week',now())
   ) ad USING (uuid, tstamp)
GROUP  BY 1, 2
ORDER  BY 1, 2

Таким образом, вы получаете все комбинации последних трех недель и UUID, увеличенные на среднюю цену — если таковая должна существовать для комбинации.
Основываясь на некоторых обоснованных догадках , чтобы восполнить недостающую информацию ..