sql - 如何在一个sql查询中生成多个时间序列？

Question

这是数据库布局。我有一张表，随着时间的推移，销售额很少，每天汇总。如果某件商品在 2015 年 1 月 1 日有 10 次销售，我将有一个条目，但如果我有 0 个，则我没有条目。像这样。

|--------------------------------------|
| day_of_year | year | sales | item_id |
|--------------------------------------|
|      01     | 2015 |  20   |   A1    |
|      01     | 2015 |  11   |   A2    | 
|      07     | 2015 |  09   |   A1    | 
|     ...     | ...  |  ...  |  ...    | 
|--------------------------------------|

这就是我获取 1 个项目的时间序列的方式。

SELECT doy, max(sales) FROM (
    SELECT day_of_year AS doy,
           sales       AS sales
      FROM myschema.entry_daily
     WHERE item_id = theNameOfmyItem
       AND year = 2015
       AND day_of_year < 150
     UNION
    SELECT doy AS doy,
           0   AS sales
      FROM generate_series(1, 149) AS doy) as t
GROUP BY doy
ORDER BY doy;

我目前使用 R 循环对每个项目进行 1 次查询。然后我将结果汇总到一个数据框中。但这非常慢。我实际上只希望有一个查询以下列形式聚合所有数据。

|----------------------------------------------|
| item_id | 01 | 02 | 03 | 04 | 05 | ... | 149 |
|----------------------------------------------|
|    A1   | 10 | 00 | 00 | 05 | 12 | ... |  11 |
|    A2   | 11 | 00 | 30 | 01 | 15 | ... |  09 |
|    A3   | 20 | 00 | 00 | 05 | 17 | ... |  20 |
|                       ...                    |
|----------------------------------------------|

这可能吗？顺便说一下，我使用的是 Postgres 数据库。

Answer 1

解决方案 1. 使用聚合的简单查询。

获得预期结果的最简单和最快的方法。很容易解析客户端程序中的 sales 列。

select item, string_agg(coalesce(sales, 0)::text, ',') sales
from (
    select distinct item_id item, doy
    from generate_series (1, 10) doy  -- change 10 to given n
    cross join entry_daily
    ) sub
left join entry_daily on item_id = item and day_of_year = doy
group by 1
order by 1;

 item |        sales         
------+----------------------
 A1   | 20,0,0,0,0,0,9,0,0,0
 A2   | 11,0,0,0,0,0,0,0,0,0
(2 rows)

解决方案 2. 动态创建 View 。

基于解决方案 1，使用 array_agg() 而不是 string_agg()。该函数创建一个具有给定列数的 View 。

create or replace function create_items_view(view_name text, days int)
returns void language plpgsql as $$
declare
    list text;
begin
    select string_agg(format('s[%s] "%s"', i::text, i::text), ',')
    into list
    from generate_series(1, days) i;

    execute(format($f$
        drop view if exists %s;
        create view %s as select item, %s
        from (
            select item, array_agg(coalesce(sales, 0)) s
            from (
                select distinct item_id item, doy
                from generate_series (1, %s) doy
                cross join entry_daily
                ) sub
            left join entry_daily on item_id = item and day_of_year = doy
            group by 1
            order by 1
        ) q
        $f$, view_name, view_name, list, days)
    );
end $$;

用法:

select create_items_view('items_view_10', 10);

select * from items_view_10;

 item | 1  | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 
------+----+---+---+---+---+---+---+---+---+----
 A1   | 20 | 0 | 0 | 0 | 0 | 0 | 9 | 0 | 0 |  0
 A2   | 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0
(2 rows)

解决方案 3. 交叉表。

易于使用，但由于需要定义行格式，所以对更多的列感到非常不舒服。

create extension if not exists tablefunc;

select * from crosstab (
    'select item_id, day_of_year, sales
    from entry_daily
    order by 1',
    'select i from generate_series (1, 10) i'
) as ct 
(item_id text, "1" int, "2" int, "3" int, "4" int, "5" int, "6" int, "7" int, "8" int, "9" int, "10" int);

 item_id | 1  | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 
---------+----+---+---+---+---+---+---+---+---+----
 A1      | 20 |   |   |   |   |   | 9 |   |   |   
 A2      | 11 |   |   |   |   |   |   |   |   |   
(2 rows)