代码之家 › 专栏 › 技术社区 › Daryn

Postgresql将查询转换为具有泛型月份参数的视图

postgresql sql

Daryn · 技术社区 · 7 年前

我有一个 很大的 查询。

在该查询我必须定义一个日期范围,例如x和y之间的日期。日期范围的定义是另一个选择查询中的选择查询。

我希望将此查询转换为视图。因此,它按月份对所有结果进行分组,例如日期介于“2000-01-01”和“2000-01-31”之间的日期。

有什么办法让它这样做吗。

where date in unique month

select * from temp_view where (max_start_date, max_end_date) overlaps ('2000-01-01', '2000-01-31')

有点难以解释。。。

这是一个示例查询

    data_table
    fruit  | start_date  |  end_date
    APPLE    2000-01-14     2000-01-20
    APPLE  | 2000-01-20  |  2000-02-05
    ORANGE | 2000-01-01  |  2000-02-10

    value_table (there is a reading every day)
   trading_date | fruit  | value
    2000-01-01   APPLE    1
    2000-01-02   APPLE    1.2
    2000-01-03   APPLE    2.2
    ...
    2000-02-15   APPLE   4.4
    2000-02-16   APPLE   5.2
    ...
    2000-01-01   ORANGE    2
    2000-01-02   ORANGE    3
    2000-01-03   ORANGE    1
    ...
    2000-02-15   ORANGE   2.4
    2000-02-16   ORANGE   2.2

查询:

    with prequery as (SELECT   value, start_date, end_date
        from data_table
            )     
            SELECT 
                month,
                some_value,
                start_date,
                end_date,
                sum(daily_value)
                FROM  prequery
                INNER JOIN value_table
                on fruit=value_table.fruit  
                and trading_date between start_date and  end_date
                ->[[[and trading_date between '2000-01-01' and '2000-01-31']]]<- dont want this.  Want it to group by months.

Month  |  value  |  start_date   | end_date   | sum   |    
2000-01   APPLE     2000-01-20    2000-02-05    (sum of all values between start and end dates in January for APPLE)
2000-02   APPLE     2000-01-14    2000-01-20    (sum of all values between start and end dates in February for APPLE)

1 回复 | 直到 7 年前

sticky bit 7 年前

您是否正在搜索可从中选择一个月的第一天和最后一天的视图?

这个将创建一个视图 months

CREATE VIEW months
AS
WITH RECURSIVE cte(i, number_of_year, number_of_month, first_day, last_day)
AS
(
SELECT 1 i,
       0 / 12 + 1970 number_of_year,
       0 % 12 + 1 number_of_month,
       (lpad((0 / 12 + 1970)::text, 4, '0') || '-' || lpad((0 % 12 + 1)::text, 2, '0') || '-01')::timestamp first_day,
       (lpad((0 / 12 + 1970)::text, 4, '0') || '-' || lpad((0 % 12 + 1)::text, 2, '0') || '-01')::timestamp + INTERVAL '1 month - 1 day' last_day
UNION ALL
SELECT i + 1 i,
       i / 12 + 1970 number_of_year,
       i % 12 + 1 number_of_month,
       (lpad((i / 12 + 1970)::text, 4, '0') || '-' || lpad((i % 12 + 1)::text, 2, '0') || '-01')::timestamp first_day,
       (lpad((i / 12 + 1970)::text, 4, '0') || '-' || lpad((i % 12 + 1)::text, 2, '0') || '-01')::timestamp + INTERVAL '1 month - 1 day' last_day
       FROM cte
       WHERE i / 12 + 1970 <= 2070
)
SELECT number_of_year,
       number_of_month,
       first_day,
       last_day
       FROM cte;

但我建议将其具体化以获得更好的性能。

CREATE TABLE months
AS
WITH RECURSIVE cte(i, number_of_year, number_of_month, first_day, last_day)
AS
(
SELECT 1 i,
       0 / 12 + 1970 number_of_year,
       0 % 12 + 1 number_of_month,
       (lpad((0 / 12 + 1970)::text, 4, '0') || '-' || lpad((0 % 12 + 1)::text, 2, '0') || '-01')::timestamp first_day,
       (lpad((0 / 12 + 1970)::text, 4, '0') || '-' || lpad((0 % 12 + 1)::text, 2, '0') || '-01')::timestamp + INTERVAL '1 month - 1 day' last_day
UNION ALL
SELECT i + 1 i,
       i / 12 + 1970 number_of_year,
       i % 12 + 1 number_of_month,
       (lpad((i / 12 + 1970)::text, 4, '0') || '-' || lpad((i % 12 + 1)::text, 2, '0') || '-01')::timestamp first_day,
       (lpad((i / 12 + 1970)::text, 4, '0') || '-' || lpad((i % 12 + 1)::text, 2, '0') || '-01')::timestamp + INTERVAL '1 month - 1 day' last_day
       FROM cte
       WHERE i / 12 + 1970 <= 2070
)
SELECT number_of_year,
       number_of_month,
       first_day,
       last_day
       FROM cte;

我想你会这样问:

SELECT first_day,
       last_day
       FROM months
       WHERE number_of_year = <year>
             AND number_of_month = <month>;

然后还创建以下索引来支持它。

CREATE INDEX months_number_of_year_number_of_month_first_day_last_day
             ON months (number_of_year,
                        number_of_month,
                        first_day,
                        last_day);