我在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 和 违规 数。因此，转置数据以获得如下所示的内容：
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表达式结合使用，以将行变成列：
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
