以平均每小时转置 SQLite 行和列

2022-01-22 00:00:00 pivot sql sqlite

我在 SQLite 中有一个名为 param_vals_breaches 的表,如下所示:

I have a table in SQLite called param_vals_breaches that looks like the following:

id    param       queue       date_time              param_val    breach_count
1     c           a           2013-01-01 00:00:00    188          7
2     c           b           2013-01-01 00:00:00    156          8
3     c           c           2013-01-01 00:00:00    100          2
4     d           a           2013-01-01 00:00:00    657          0
5     d           b           2013-01-01 00:00:00    23           6
6     d           c           2013-01-01 00:00:00    230          12
7     c           a           2013-01-01 01:00:00    100          0
8     c           b           2013-01-01 01:00:00    143          9
9     c           c           2013-01-01 01:00:00    12           2
10    d           a           2013-01-01 01:00:00    0            1
11    d           b           2013-01-01 01:00:00    29           5
12    d           c           2013-01-01 01:00:00    22           14
13    c           a           2013-01-01 02:00:00    188          7
14    c           b           2013-01-01 02:00:00    156          8
15    c           c           2013-01-01 02:00:00    100          2
16    d           a           2013-01-01 02:00:00    657          0
17    d           b           2013-01-01 02:00:00    23           6
18    d           c           2013-01-01 02:00:00    230          12

我想编写一个查询,它会显示一个特定队列(例如a"),平均 param_val 和 breach_count每个参数按小时计算.因此,转置数据以获得如下所示的内容:

I want to write a query that will show me a particular queue (e.g. "a") with the average param_val and breach_count for each param on an hour by hour basis. So transposing the data to get something that looks like this:

Results for Queue A

         Hour 0         Hour 0              Hour 1         Hour 1              Hour 2         Hour 2
param    avg_param_val  avg_breach_count    avg_param_val  avg_breach_count    avg_param_val  avg_breach_count
c        xxx            xxx                 xxx            xxx                 xxx            xxx
d        xxx            xxx                 xxx            xxx                 xxx            xxx  

这可能吗?我不知道该怎么做.谢谢!

is this possible? I'm not sure how to go about it. Thanks!

推荐答案

SQLite 没有 PIVOT 函数,但您可以使用带有 CASE 表达式的聚合函数将行转换为列:

SQLite does not have a PIVOT function but you can use an aggregate function with a CASE expression to turn the rows into columns:

select param,
  avg(case when time = '00' then param_val end) AvgHour0Val,
  avg(case when time = '00' then breach_count end) AvgHour0Count,
  avg(case when time = '01' then param_val end) AvgHour1Val,
  avg(case when time = '01' then breach_count end) AvgHour1Count,
  avg(case when time = '02' then param_val end) AvgHour2Val,
  avg(case when time = '02' then breach_count end) AvgHour2Count
from
(
  select param,
    strftime('%H', date_time) time,
    param_val,
    breach_count
  from param_vals_breaches
  where queue = 'a'
) src
group by param;

参见 SQL Fiddle with Demo

相关文章