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

发布时间：2020-12-12 19:12:57 所属栏目：百科来源：网络整理

导读：我在SQLite中有一个名为param_vals_breaches的表,如下所示： id param queue date_time param_val breach_count1 c a 2013-01-01 00:00:00 188 72 c b 2013-01-01 00:00:00 156 83 c c 2013-01-01 00:00:00 100 24 d a 2013-01-01 00:00:00 657 05 d b 2013-

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

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.因此,转换数据以获得如下所示的内容：

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

这可能吗？我不知道该怎么做.谢谢！

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!