sql - Postgresql 查询 - 每月平均值

Question

我有两个要实现的查询。

我有一张这样的 table ......

Date        | Period | Location | Price
2017-01-01      1         A         10
2017-01-01      2         A         15
2017-01-01      1         B         15
2017-01-01      2         B         16

每个日期有 48 个读数(每半小时一个)。

查询 1:我希望按以下格式获取日期范围内任何给定位置的平均价格:

例如2017-01-01 和 2017-06-30 之间，位置“A”的每期平均价格

  Period 1  | Period 2|  Period 3 ...
       10           11          15

查询 2:我希望任何给定位置的平均价格可以追溯到 x 月数:(周期无关紧要)

Month   |  Average price
january        10
february       12
march          16

任何帮助将不胜感激:)

Answer 1

您列出的第二个查询是一个非常简单的 group by 操作。唯一有点棘手的是您需要从列出的日期中提取月份。

select
    date_part('month', t.Date) as month,
    avg(t.Price) as average_price
from
    mytable t
group by
    date_part('month', t.Date)

---

然而，第一个查询要复杂得多。它涉及 crosstab()，如果您还没有在数据库中启用它，则必须启用它。总体思路是计算每个时期的平均值，然后像在 Excel 中一样旋转数据。

select * from crosstab(
'   select
        t.Period
        , avg(t.Price) as avg_price
    from
        mytable t
    group by
        t.Period
    order by
        1
        , 2
'
) as ct(
    "Period" text
    , "1"  int
    , "2"  int
    , "3"  int
    , "4"  int
    , "5"  int
    , "6"  int
    , "7"  int
    , "8"  int
    , "9"  int
    , "10" int
    , "11" int
    , "12" int
)

这answer有大量关于 Postgre 的 crosstab()

的资料