Skip to main content
Stack Overflow
  1. About
  3. For Teams

Return to Answer

added 467 characters in body
Source Link
ValNik
  • 7.9k
  • 1
  • 9
  • 18
 ,b.value->>'date' app_date
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start

 ,b.value->>'date' app_date
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start

 (date||' '||(a->>'granula_start'))::timestamp granula_start
added 467 characters in body
Source Link
ValNik
  • 7.9k
  • 1
  • 9
  • 18
  1. The result should be based on a table with granules 15 minutes long in the interval specified by the parameters.
generate_series('2025-09-02 14:00:00'::timestamp,'2025-09-02 18:00:00'::timestamp,'15 minute'::interval) grn15
  1. Expand source JSONB
    2.1 Extract "apps" array from "app_info". Use LATERAL JOIN to use app_info column from main table. 2.2 Extract granula_start and granula_end for 5 minute intervals
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
  1. Exact granula_start is app_date+granula_start
 ,b.value->>'date' app_date
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start
  1. Join rows to generated_series output on granula_start
  2. Aggregate data

Expand JSONB column to table.

fiddle
and

  1. The result should be based on a table with granules 15 minutes long in the interval specified by the parameters.
generate_series('2025-09-02 14:00:00'::timestamp,'2025-09-02 18:00:00'::timestamp,'15 minute'::interval) grn15
  1. Expand source JSONB
    2.1 Extract "apps" array from "app_info". Use LATERAL JOIN to use app_info column from main table. 2.2 Extract granula_start and granula_end for 5 minute intervals
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
  1. Exact granula_start is app_date+granula_start
 ,b.value->>'date' app_date
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start
  1. Join rows to generated_series output on granula_start
  2. Aggregate data

Expand JSONB column to table.

fiddle
and

Source Link
ValNik
  • 7.9k
  • 1
  • 9
  • 18

See example

select grn15
 ,count(app_name) app_count
 ,sum(count_seconds::float) seconds_count
from(
select *
from generate_series('2025-09-02 14:00:00'::timestamp,'2025-09-02 18:00:00'::timestamp,'15 minute'::interval) grn15
left join(
select employee_id meid, date -- ,app_info
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start
 ,(date||' '||(a->>'granula_end'))::timestamp granula_end
 ,b.value->>'date' app_date
 ,b.value->>'app_name' app_name
 ,b.value->>'type_name' type_name
 ,b.value->>'domain_site' domain_site
 ,b.value->>'employee_id' app_employee_id
 ,b.value->>'count_seconds' count_seconds
 ,b.value app_data
from my_temp
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
)q1 on q1.granula_start::timestamp>=grn15 and q1.granula_end::timestamp< (grn15+'15 minute'::interval)
)q2
group by grn15
order by grn15 -- ,granula_start
grn15 app_count seconds_count
2025年09月02日 14:00:00 0 null
2025年09月02日 14:15:00 0 null
2025年09月02日 14:30:00 0 null
2025年09月02日 14:45:00 4 1371.9560000000001
2025年09月02日 15:00:00 0 null
2025年09月02日 15:15:00 0 null
2025年09月02日 15:30:00 0 null
2025年09月02日 15:45:00 0 null
2025年09月02日 16:00:00 0 null
2025年09月02日 16:15:00 0 null
2025年09月02日 16:30:00 0 null
2025年09月02日 16:45:00 0 null
2025年09月02日 17:00:00 0 null
2025年09月02日 17:15:00 0 null
2025年09月02日 17:30:00 2 111.471
2025年09月02日 17:45:00 2 13.51
2025年09月02日 18:00:00 0 null 
select grn15, meid, date, granula_start, granula_end, count_seconds, app_date, app_name
from generate_series('2025-09-02 14:00:00'::timestamp,'2025-09-02 18:00:00'::timestamp,'15 minute'::interval) grn15
left join(
select employee_id meid, date -- ,app_info
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start
 ,(date||' '||(a->>'granula_end'))::timestamp granula_end
 ,b.value->>'date' app_date
 ,b.value->>'app_name' app_name
 ,b.value->>'type_name' type_name
 ,b.value->>'domain_site' domain_site
 ,b.value->>'employee_id' app_employee_id
 ,b.value->>'count_seconds' count_seconds
 ,b.value app_data
from my_temp
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
)q1 on q1.granula_start::timestamp>=grn15 and q1.granula_end::timestamp< (grn15+'15 minute'::interval)
order by grn15,granula_start
grn15 meid date granula_start granula_end count_seconds app_date app_name
2025年09月02日 14:00:00 null null null null null null null
2025年09月02日 14:15:00 null null null null null null null
2025年09月02日 14:30:00 null null null null null null null
2025年09月02日 14:45:00 1 2025年09月02日 2025年09月02日 14:45:00 2025年09月02日 14:50:00 155.334 2025年07月31日 Postman
2025年09月02日 14:45:00 1 2025年09月02日 2025年09月02日 14:50:00 2025年09月02日 14:55:00 282.565 2025年07月31日 CLion
2025年09月02日 14:45:00 1 2025年09月02日 2025年09月02日 14:50:00 2025年09月02日 14:55:00 459.091 2025年07月31日 WinSCP: SFTP, FTP, WebDAV, S3 and SCP client
2025年09月02日 14:45:00 1 2025年09月02日 2025年09月02日 14:50:00 2025年09月02日 14:55:00 474.966 2025年07月31日 Explorer
2025年09月02日 15:00:00 null null null null null null null
2025年09月02日 15:15:00 null null null null null null null
2025年09月02日 15:30:00 null null null null null null null
2025年09月02日 15:45:00 null null null null null null null
2025年09月02日 16:00:00 null null null null null null null
2025年09月02日 16:15:00 null null null null null null null
2025年09月02日 16:30:00 null null null null null null null
2025年09月02日 16:45:00 null null null null null null null
2025年09月02日 17:00:00 null null null null null null null
2025年09月02日 17:15:00 null null null null null null null
2025年09月02日 17:30:00 1 2025年09月02日 2025年09月02日 17:35:00 2025年09月02日 17:40:00 66.195 2025年07月31日 CLion
2025年09月02日 17:30:00 1 2025年09月02日 2025年09月02日 17:35:00 2025年09月02日 17:40:00 45.276 2025年07月31日 Postman
2025年09月02日 17:45:00 1 2025年09月02日 2025年09月02日 17:50:00 2025年09月02日 17:55:00 3.86 2025年07月31日 CLion
2025年09月02日 17:45:00 1 2025年09月02日 2025年09月02日 17:50:00 2025年09月02日 17:55:00 9.65 2025年07月31日 Postman
2025年09月02日 18:00:00 null null null null null null null 
select employee_id meid, date -- ,app_info
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start
 ,(date||' '||(a->>'granula_end'))::timestamp granula_end
-- ,b.value->>'date' app_date
 ,b.value->>'app_name' app_name
-- ,b.value->>'type_name' type_name
-- ,b.value->>'domain_site' domain_site
-- ,b.value->>'employee_id' app_employee_id
 ,b.value->>'count_seconds' count_seconds
 ,jsonb_pretty(b.value) app_data
from my_temp
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
order by meid,date, granula_start
meid date granula_start granula_end app_name count_seconds app_data
1 2025年09月02日 2025年09月02日 14:45:00 2025年09月02日 14:50:00 Postman 155.334 {
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 155.334
}
1 2025年09月02日 2025年09月02日 14:50:00 2025年09月02日 14:55:00 CLion 282.565 {
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 282.565
}
1 2025年09月02日 2025年09月02日 14:50:00 2025年09月02日 14:55:00 WinSCP: SFTP, FTP, WebDAV, S3 and SCP client 459.091 {
"date": "2025年07月31日",
"app_name": "WinSCP: SFTP, FTP, WebDAV, S3 and SCP client",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 459.091
}
1 2025年09月02日 2025年09月02日 14:50:00 2025年09月02日 14:55:00 Explorer 474.966 {
"date": "2025年07月31日",
"app_name": "Explorer",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 474.966
}
1 2025年09月02日 2025年09月02日 16:10:00 2025年09月02日 16:15:00 CLion 243.496 {
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 243.496
}
1 2025年09月02日 2025年09月02日 16:10:00 2025年09月02日 16:15:00 Postman 319.902 {
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 319.902
}
1 2025年09月02日 2025年09月02日 16:55:00 2025年09月02日 17:00:00 CLion 192.897 {
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 192.897
}
1 2025年09月02日 2025年09月02日 16:55:00 2025年09月02日 17:00:00 Postman 111.307 {
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 111.307
}
1 2025年09月02日 2025年09月02日 16:55:00 2025年09月02日 17:00:00 Explorer 144.277 {
"date": "2025年07月31日",
"app_name": "Explorer",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 144.277
}
1 2025年09月02日 2025年09月02日 17:35:00 2025年09月02日 17:40:00 CLion 66.195 {
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 66.195
}
1 2025年09月02日 2025年09月02日 17:35:00 2025年09月02日 17:40:00 Postman 45.276 {
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 45.276
}
1 2025年09月02日 2025年09月02日 17:50:00 2025年09月02日 17:55:00 CLion 3.86 {
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 3.86
}
1 2025年09月02日 2025年09月02日 17:50:00 2025年09月02日 17:55:00 Postman 9.65 {
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 9.65
}

fiddle

lang-sql

AltStyle によって変換されたページ (->オリジナル) /