查询数据

加载数据集后，您可以开始构建一些查询，以发现您的数据告诉您什么。本教程使用 Timescale 超函数来构建标准 PostgreSQL 中不可能实现的查询。

在本节中，您将学习如何构建查询，以回答以下问题

一天中每小时的能源消耗是多少？

当您为能源消耗数据设置数据库后，您可以构建一个查询，以查找典型一天中每小时能源消耗的中位数和最大值。

查找每小时消耗多少千瓦时的能量

连接到包含能源消耗数据集的 Timescale 数据库。

在 psql 提示符下，使用 Timescale Toolkit 功能来计算第 50 个百分位数或中位数。然后使用标准 PostgreSQL max 函数计算最大能源消耗


WITH per_hour AS (
SELECT
time,
value
FROM kwh_hour_by_hour
WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year'
ORDER BY 1
), hourly AS (
 SELECT
      extract(HOUR FROM time) * interval '1 hour' as hour,
      value
 FROM per_hour
)
SELECT
    hour,
    approx_percentile(0.50, percentile_agg(value)) as median,
    max(value) as maximum
FROM hourly
GROUP BY 1
ORDER BY 1;

您获得的数据看起来有点像这样


hour   |       median       | maximum
    ----------+--------------------+---------
     00:00:00 | 0.5998949812512439 |     0.6
     01:00:00 | 0.5998949812512439 |     0.6
     02:00:00 | 0.5998949812512439 |     0.6
     03:00:00 | 1.6015944383271534 |     1.9
     04:00:00 | 2.5986701108275327 |     2.7
     05:00:00 | 1.4007385207185301 |     3.4
     06:00:00 | 0.5998949812512439 |     2.7
     07:00:00 | 0.6997720645753496 |     0.8
     08:00:00 | 0.6997720645753496 |     0.8
     09:00:00 | 0.6997720645753496 |     0.8
     10:00:00 | 0.9003240409125329 |     1.1
     11:00:00 | 0.8001143897618259 |     0.9

一周中每天的能源消耗是多少？

您还可以查看周末和工作日之间的能源消耗变化。

查找工作日的能源消耗

连接到包含能源消耗数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询查找工作日和周末的消耗差异


WITH per_day AS (
 SELECT
   time,
   value
 FROM kwh_day_by_day
 WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year'
 ORDER BY 1
), daily AS (
    SELECT
       to_char(time, 'Dy') as day,
       value
    FROM per_day
), percentile AS (
    SELECT
        day,
        approx_percentile(0.50, percentile_agg(value)) as value
    FROM daily
    GROUP BY 1
    ORDER BY 1
)
SELECT
    d.day,
    d.ordinal,
    pd.value
FROM unnest(array['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']) WITH ORDINALITY AS d(day, ordinal)
LEFT JOIN percentile pd ON lower(pd.day) = lower(d.day);

您获得的数据看起来有点像这样


day | ordinal |       value
-----+---------+--------------------
 Mon |       2 |  23.08078714975423
 Sun |       1 | 19.511430831944395
 Tue |       3 | 25.003118897837307
 Wed |       4 |   8.09300571759772
 Sat |       7 |
 Fri |       6 |
 Thu |       5 |

每月的能源消耗是多少？

您可能还想查看每月发生的能源消耗。

查找一年中每个月的能源消耗

连接到包含能源消耗数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询查找一年中每个月的消耗量


WITH per_day AS (
 SELECT
   time,
   value
 FROM kwh_day_by_day
 WHERE "time" > now() - interval '1 year'
 ORDER BY 1
), per_month AS (
   SELECT
      to_char(time, 'Mon') as month,
       sum(value) as value
   FROM per_day
  GROUP BY 1
)
SELECT
   m.month,
   m.ordinal,
   pd.value
FROM unnest(array['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']) WITH ORDINALITY AS m(month, ordinal)
LEFT JOIN per_month pd ON lower(pd.month) = lower(m.month)
ORDER BY ordinal;

您获得的数据看起来有点像这样


month | ordinal |       value
    -------+---------+-------------------
    Jan   |       1 |
    Feb   |       2 |
    Mar   |       3 |
    Apr   |       4 |
    May   |       5 | 75.69999999999999
    Jun   |       6 |
    Jul   |       7 |
    Aug   |       8 |
    Sep   |       9 |
    Oct   |      10 |
    Nov   |      11 |
    Dec   |      12 |

可选要在 Grafana 中可视化此数据，请创建一个新面板，并选择 条形图 可视化。选择能源消耗数据集作为您的数据源，并键入上一步中的查询。在 格式为 部分中，选择 表格。
可选选择一种配色方案，以便以不同的颜色显示不同的消耗量。在选项面板中，在 标准选项 下，将 颜色方案 更改为有用的 按值 范围。