主页 / dba / 问题 / 123824
Accepted
Kyle Decot
Asked: 2015-12-17 04:55:18 +0800 CST

用于 postgresql 中的多种记录类型的 generate_series

  • 772

我有两个要查询的表：pest_counts它们pests看起来像：

CREATE TABLE pests(id,name)
AS VALUES
  (1,'Thrip'),
  (2,'Fungus Gnosts');

CREATE TABLE pest_counts(id,pest_id,date,count)
AS VALUES
  (1,1,'2015-01-01'::date,14),
  (2,2,'2015-01-02'::date,5);

我想使用 postgresgenerate_series来显示为日期系列找到的每种害虫的数量：

预期成绩

name         | date       | count
-------------+------------+-------
Thrip        | 2015-01-01 | 14
Thrip        | 2015-01-02 | 0
....
Fungus Gnats | 2015-01-01 | 0
Fungus Gnats | 2015-01-02 | 5
...

我知道我需要类似以下的东西，但我不确定如何做剩下的：

SELECT date FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 day') date
postgresql

2 个回答

  1. Best Answer

    我通常通过为所有可能的数据点（这里是害虫和日期）建立一个表格来解决这些问题。这可以通过 a 轻松实现CROSS JOIN，请参见WITH下面的查询。

    然后，作为最后一步，我只是（外部）加入现有的测量值，基于害虫 ID 和日期 - 可选地通过COALESCE().

    所以，整个查询是：

    WITH data_points AS (
    SELECT id, name, i::date
    FROM pests
    CROSS JOIN generate_series('2015-01-01'::date, '2015-01-05', '1 day') t(i)
) 
SELECT d.name, d.i, COALESCE(p.cnt, 0) 
FROM data_points AS d 
LEFT JOIN pest_counts AS p 
    ON d.id = p.pest_id 
    AND d.i = p.count_date;

    在SQLFiddle上检查它。

    注意：当表格或生成的系列很大时，在CROSS JOINCTE 内部进行可能不是一个好主意。（无论是否有给定日期的数据，它都必须实现所有行）。在这种情况下，应该在FROM子句中做同样的事情，作为带括号的子连接，而不是当前对data_points. 这样，规划者可以更好地了解受影响的行以及使用索引的可能性。我在示例中使用 CTE，因为它看起来更简洁。

    • 8

  2. 我会建议您下次使用 fiddle.com 以便有一个在线模式可以玩。

    generate_series 函数返回一组时间戳，因此您需要将其转换为函数外部的日期。这在当前查询中是必要的，因为它与表中的timestamp不匹配。datepest_counts

    sandbox=# \df generate_series
   Schema   |      Name       |         Result data type          |                        Argument data types                         |  Type  
(...)
 pg_catalog | generate_series | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, interval | normal
 pg_catalog | generate_series | SETOF timestamp with time zone    | timestamp with time zone, timestamp with time zone, interval       | normal
(6 rows)

    我会建议类似的东西：

    SELECT p.name, pc.date, pc.count 
FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 day') days 
join pest_counts pc ON (days::date = pc.date) 
join pests p ON (p.id = pc.pest_id) ;
    • 0

相关问题