I have a table with millions of data. I'm having trouble making reports on data.
This is the table I have:
"channel_id" "datetime" "parameter" "raw"
10 "2022-12-02 16:16:00" "Günlük Debi" 3423.89
9 "2022-12-02 16:16:00" "KABIN NEM" 36.27
8 "2022-12-02 16:16:00" "KABIN SICAKLIK" 20.18
7 "2022-12-02 16:16:00" "AKM" 4.54
6 "2022-12-02 16:16:00" "KOi" 24.4
5 "2022-12-02 16:16:00" "AkisHizi" 0.59
4 "2022-12-02 16:16:00" "Sicaklik" 13.53
3 "2022-12-02 16:16:00" "Debi" 3.04
2 "2022-12-02 16:16:00" "CozunmusOksijen" 5.05
1 "2022-12-02 16:16:00" "Iletkenlik" 1125.64
0 "2022-12-02 16:16:00" "pH" 7.09
9 "2022-12-02 16:17:00" "KABIN NEM" 20.22
8 "2022-12-02 16:17:00" "KABIN SICAKLIK" 6.49
7 "2022-12-02 16:17:00" "AKM" 6.36
6 "2022-12-02 16:17:00" "KOi" 30.12
5 "2022-12-02 16:17:00" "AkisHizi" 0.82
4 "2022-12-02 16:17:00" "Sicaklik" 20.36
3 "2022-12-02 16:17:00" "Debi" 16.15
2 "2022-12-02 16:17:00" "CozunmusOksijen" 2.45
1 "2022-12-02 16:17:00" "Iletkenlik" 1570.75
0 "2022-12-02 16:17:00" "pH" 7.48
7 "2022-12-02 16:13:00" "AKM" 16.02
6 "2022-12-02 16:13:00" "KOi" 25.98
5 "2022-12-02 16:13:00" "AkisHizi" 0.83
4 "2022-12-02 16:13:00" "Sicaklik" 17.87
3 "2022-12-02 16:13:00" "Debi" 27.85
2 "2022-12-02 16:13:00" "CozunmusOksijen" 5.91
1 "2022-12-02 16:13:00" "Iletkenlik" 2221.36
0 "2022-12-02 16:13:00" "pH" 7.25
9 "2022-12-02 16:14:00" "KABIN NEM" 62.28
8 "2022-12-02 16:14:00" "KABIN SICAKLIK" 13.99
7 "2022-12-02 16:14:00" "AKM" 6.02
6 "2022-12-02 16:14:00" "KOi" 21.36
5 "2022-12-02 16:14:00" "AkisHizi" 0.56
4 "2022-12-02 16:14:00" "Sicaklik" 21.6
3 "2022-12-02 16:14:00" "Debi" 10.35
2 "2022-12-02 16:14:00" "CozunmusOksijen" 0.32
1 "2022-12-02 16:14:00" "Iletkenlik" 7325.54
0 "2022-12-02 16:14:00" "pH" 7.57
10 "2022-12-02 16:15:00" "Günlük Debi" 5363.51
9 "2022-12-02 16:15:00" "KABIN NEM" 34.65
8 "2022-12-02 16:15:00" "KABIN SICAKLIK" 20.25
7 "2022-12-02 16:15:00" "AKM" 6.52
6 "2022-12-02 16:15:00" "KOi" 12.71
5 "2022-12-02 16:15:00" "AkisHizi" 0.54
4 "2022-12-02 16:15:00" "Sicaklik" 14.41
3 "2022-12-02 16:15:00" "Debi" 5.09
2 "2022-12-02 16:15:00" "CozunmusOksijen" 5.86
1 "2022-12-02 16:15:00" "Iletkenlik" 1933.55
0 "2022-12-02 16:15:00" "pH" 7.24
7 "2022-12-02 16:13:00" "AKM" 38.64
6 "2022-12-02 16:13:00" "KOi" 26.17
5 "2022-12-02 16:13:00" "AkisHizi" 0.52
4 "2022-12-02 16:13:00" "Sicaklik" 12.46
3 "2022-12-02 16:13:00" "Debi" 1.32
2 "2022-12-02 16:13:00" "CozunmusOksijen" 9.06
1 "2022-12-02 16:13:00" "Iletkenlik" 2566.5
0 "2022-12-02 16:13:00" "pH" 7.33
9 "2022-12-02 16:14:00" "KABIN NEM" 21.71
8 "2022-12-02 16:14:00" "KABIN SICAKLIK" 16.5
7 "2022-12-02 16:14:00" "AKM" 12.56
6 "2022-12-02 16:14:00" "KOi" 18.64
5 "2022-12-02 16:14:00" "AkisHizi" 0.63
4 "2022-12-02 16:14:00" "Sicaklik" 12.56
3 "2022-12-02 16:14:00" "Debi" 4.84
2 "2022-12-02 16:14:00" "CozunmusOksijen" 2.15
1 "2022-12-02 16:14:00" "Iletkenlik" 621.05
0 "2022-12-02 16:14:00" "pH" 5.16
9 "2022-12-02 16:14:00" "KABIN NEM" 20.65
8 "2022-12-02 16:14:00" "KABIN SICAKLIK" 21.32
7 "2022-12-02 16:14:00" "AKM" 9.28
6 "2022-12-02 16:14:00" "KOi" 23.24
5 "2022-12-02 16:14:00" "AkisHizi" 0.63
4 "2022-12-02 16:14:00" "Sicaklik" 12.79
3 "2022-12-02 16:14:00" "Debi" 3.09
2 "2022-12-02 16:14:00" "CozunmusOksijen" 2.53
1 "2022-12-02 16:14:00" "Iletkenlik" 1473.54
0 "2022-12-02 16:14:00" "pH" 7.69
10 "2022-12-02 16:14:00" "Günlük Debi" 8453.81
9 "2022-12-02 16:14:00" "KABIN NEM" 32.88
8 "2022-12-02 16:14:00" "KABIN SICAKLIK" 24.88
7 "2022-12-02 16:14:00" "AKM" 6.16
6 "2022-12-02 16:14:00" "KOi" 51.93
5 "2022-12-02 16:14:00" "AkisHizi" 0.54
4 "2022-12-02 16:14:00" "Sicaklik" 17.91
3 "2022-12-02 16:14:00" "Debi" 9.3
2 "2022-12-02 16:14:00" "CozunmusOksijen" 2.69
1 "2022-12-02 16:14:00" "Iletkenlik" 2318.17
0 "2022-12-02 16:14:00" "pH" 7.27
10 "2022-12-02 16:14:00" "Günlük Debi" 3342.46
9 "2022-12-02 16:14:00" "KABIN NEM" 57.81
8 "2022-12-02 16:14:00" "KABIN SICAKLIK" 42.21
7 "2022-12-02 16:14:00" "AKM" 14.7
6 "2022-12-02 16:14:00" "KOi" 38.02
5 "2022-12-02 16:14:00" "AkisHizi" 0.61
4 "2022-12-02 16:14:00" "Sicaklik" 19.88
3 "2022-12-02 16:14:00" "Debi" 3.39
2 "2022-12-02 16:14:00" "CozunmusOksijen" 3.94
1 "2022-12-02 16:14:00" "Iletkenlik" 901.02
0 "2022-12-02 16:14:00" "pH" 7.33
The result I want to achieve is like this:
datetime values
2022-12-02 16:16:00 [{..PULSAR,Günlük Debi,3423.89},{...GENTEK...}...]
2022-12-02 16:17:00 [{..Pi,pH,7.09},{...GENTEK...}...]
. . .
I want to group data recorded on the same date in one row.
How can I achieve this? Is there a way?
I pulled the data by time period and then grouped it with a python for loop, but this was a very long process in large time intervals.
CodePudding user response：
Assume you meant to group data on the same value of datetime column, you can do this:
select datetime,
array_to_json(array_agg(json_build_object(parameter, raw))) as parameters
from a_table
group by 1
order by 1;
Result:
datetime |parameters |
----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2022-12-02 16:13:00.000|[{"AKM" : 16.02},{"KOi" : 25.98},{"AkisHizi" : 0.83},{"Sicaklik" : 17.87},{"Debi" : 27.85},{"CozunmusOksijen" : 5.91},{"Iletkenlik" : 2221.36},{"pH" : 7.25},{"AKM" : 38.64},{"KOi" : 26.17},{"AkisHizi" : 0.52},{"Sicaklik" : 12.46},{"Debi" : 1.32},{"Cozunmu|
2022-12-02 16:14:00.000|[{"KABIN NEM" : 62.28},{"KABIN SICAKLIK" : 13.99},{"AKM" : 6.02},{"KOi" : 21.36},{"AkisHizi" : 0.56},{"Sicaklik" : 21.6},{"Debi" : 10.35},{"CozunmusOksijen" : 0.32},{"Iletkenlik" : 7325.54},{"pH" : 7.57},{"KABIN NEM" : 21.71},{"KABIN SICAKLIK" : 16.5},{"A|
2022-12-02 16:15:00.000|[{"Günlük Debi" : 5363.51},{"KABIN NEM" : 34.65},{"KABIN SICAKLIK" : 20.25},{"AKM" : 6.52},{"KOi" : 12.71},{"AkisHizi" : 0.54},{"Sicaklik" : 14.41},{"Debi" : 5.09},{"CozunmusOksijen" : 5.86},{"Iletkenlik" : 1933.55},{"pH" : 7.24}] |
2022-12-02 16:16:00.000|[{"Günlük Debi" : 3423.89},{"KABIN NEM" : 36.27},{"KABIN SICAKLIK" : 20.18},{"AKM" : 4.54},{"KOi" : 24.4},{"AkisHizi" : 0.59},{"Sicaklik" : 13.53},{"Debi" : 3.04},{"CozunmusOksijen" : 5.05},{"Iletkenlik" : 1125.64},{"pH" : 7.09}] |
2022-12-02 16:17:00.000|[{"KABIN NEM" : 20.22},{"KABIN SICAKLIK" : 6.49},{"AKM" : 6.36},{"KOi" : 30.12},{"AkisHizi" : 0.82},{"Sicaklik" : 20.36},{"Debi" : 16.15},{"CozunmusOksijen" : 2.45},{"Iletkenlik" : 1570.75},{"pH" : 7.48}] |