0

I am currently uploading my solar inverter data to PVOutput using a Linux machine and a scratch built Perl script. I want to add the meter data from my enrgy supplier. I have an 87,000 row CSV file downloaded from my energy supplier that contains the following (sample) data:

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

By creating a virtual column (MyDate) and using the following select:

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

I have been able to massage the data in SQLite to give me:

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

But for upload to PVOutput, the format I'm looking for is:

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

As you can see, my SQL skills are weak, and any help would be most appreciated.

Phill.

0

Simple conditional aggregation should do the trick:

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

An alternative syntax is to remove the filter and use case

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.