主页 / dba / 问题 / 300138
Accepted
user2058625
Asked: 2021-09-25 14:39:07 +0800 CST

SQLite 聚合然后转置

  • 772

我目前正在使用 Linux 机器和临时构建的 Perl 脚本将我的太阳能逆变器数据上传到 PVOutput。我想添加来自我的能源供应商的仪表数据。我从我的能源供应商处下载了一个 87,000 行的 CSV 文件，其中包含以下（样本）数据：

NMI,Meter Serial Number,Stream,Register Number,Register Description,UOM,ReadingDate,Units,Read Type<br>
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:45,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:45,0.093,  Actual
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:30,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:30,0.085,  Actual
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:15,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:15,0.085,  Actual

通过创建一个虚拟列 (MyDate) 并使用以下选择：

select MyDate, "Register Description", sum(Units) 
from MeterData group by MyDate, "Register Description"

我已经能够按摩 SQLite 中的数据给我：

20210920|Offpeak|3.821
20210920|Peak|4.954
20210920|Shoulder|5.695
20210920|Solar energy|2.958
20210921|Offpeak|3.831
20210921|Peak|4.41
20210921|Shoulder|4.428
20210921|Solar energy|4.354
20210922|Offpeak|4.355
20210922|Peak|6.572
20210922|Shoulder|1.961
20210922|Solar energy|7.217

但是对于上传到 PVOutput，我正在寻找的格式是：

20210919|Offpeak|3.514|Peak|8.453|Shoulder|7.445|Solar energy|2.792
20210920|Offpeak|3.821|Peak|4.954|Shoulder|5.695|Solar energy|2.958
20210921|Offpeak|3.831|Peak|4.410|Shoulder|4.428|Solar energy|4.354
20210922|Offpeak|4.355|Peak|6.572|Shoulder|1.961|Solar energy|7.217

如您所见，我的 SQL 技能很弱，任何帮助将不胜感激。

菲尔。

aggregate pivot

1 个回答

  1. Best Answer

    简单的条件聚合应该可以解决问题：

    select
  MyDate,
  'Offpeak',
  sum(Units) filter (where "Register Description" = 'Offpeak'),
  'Peak',
  sum(Units) filter (where "Register Description" = 'Peak'),
  'Shoulder',
  sum(Units) filter (where "Register Description" = 'Shoulder'),
  'Solar energy',
  sum(Units) filter (where "Register Description" = 'Solar energy')
from MeterData
group by MyDate

    另一种语法是删除filter和使用case

    sum(case when "Register Description" = 'Offpeak' then Units end)
    • 0

相关问题