|
212 min
48%
|
2,823 ms
|
4,501
postgres
|
--table-users
WITH data AS (
SELECT DISTINCT ON (user_id)
user_id,
id,
created_at,
counter_id
FROM sessions
WHERE space_id = $1
AND user_id IS NOT NULL
AND branch_id IS NOT NULL
AND counter_id IS NOT NULL
AND status = $3
),
ticket_count AS (
SELECT user_id,
COUNT(*) AS ticket_count,
COUNT(DISTINCT service_id) as total_services,
COALESCE(SUM((stats->>$4)::integer), $5) AS total_attending_time_s
FROM ticket_archive
WHERE state = $6
and space_id = $2
AND user_id IN (SELECT user_id FROM data)
GROUP BY user_id
),
data2 AS (
SELECT data.user_id,
data.created_at,
users.name AS user_name,
users.email AS user_email,
counters.name AS counter_name
FROM data
INNER JOIN users ON data.user_id = users.id
LEFT JOIN counters ON data.counter_id = counters.id
)
SELECT json_agg(jsonb_build_object(
$7, data2.created_at,
$8, data2.user_name,
$9, data2.user_email,
$10, data2.counter_name,
$11, COALESCE(ticket_count.ticket_count, $12),
$13, ticket_count.total_services,
$14, ticket_count.total_attending_time_s
))::jsonb as aggdata
FROM data2
LEFT JOIN ticket_count ON data2.user_id = ticket_count.user_id
|
|
58 min
13%
|
854 ms
|
4,043
postgres
|
--tickets-stats-total
WITH data1 AS (
SELECT COALESCE(sum((values->$4)::numeric), $5) AS total_count,
round(sum((values->$6)::numeric) / sum((values->$7)::numeric), $8) AS waiting_average,
round(sum((values->$9)::numeric) / sum((values->$10)::numeric), $11) AS attending_average
FROM ticket_stats
WHERE space_id = $1
AND (keys->>$12)::integer in ($13)
AND tr <@ numrange($14, $15)
),
data2 AS (
SELECT
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $16 = $17 ), $18)
AS waiting_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $19 = $20 ), $21)
AS attending_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $22 = $23), $24)
AS expired_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $25 = $26), $27)
AS cancelled_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $28 = $29), $30)
AS paused_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $31 = $32), $33)
AS terminated_count
FROM ticket_archive
LEFT JOIN LATERAL (select elem from jsonb_array_elements(ticket_archive.path) as elem) sub
ON ticket_archive.path IS NOT NULL AND ticket_archive.path != $34::jsonb
WHERE space_id = $2
AND branch_id in ($35)
-- AND EXTRACT(epoch FROM created_at)::numeric <@ numrange('1764547200', '1767225600')
AND created_at >= to_timestamp($36)
AND created_at < to_timestamp($37)
),
data3 AS (
select
coalesce(sum((stats->>$38)::integer), $39) as slot_noshow_count,
coalesce(sum((stats->>$40)::integer), $41) as slot_booked_count,
coalesce(sum((stats->>$42)::integer), $43) as slot_checked_count
from slot
where space_id = $3
and numrange(start_time_ts, end_time_ts) <@ numrange($44, $45)
)
select json_agg(data4)::jsonb as aggdata
from (
SELECT
data1.total_count,
data1.waiting_average,
data1.attending_average,
data2.waiting_count,
data2.attending_count,
data2.paused_count,
data2.terminated_count,
data2.expired_count,
data2.cancelled_count,
data3.slot_noshow_count,
data3.slot_booked_count,
data3.slot_checked_count
FROM data1, data2, data3
) data4
|
|
32 min
7%
|
118 ms
|
16,281
postgres
|
SELECT COUNT(*) OVER(),
MAX(id) OVER() as maxid,
MIN(id) OVER() as minid,
MAX(created_at) OVER() as maxdate,
MIN(created_at) OVER() as mindate,
id, ticket_id, state, branch_session_id, service_id, counter_id, user_id, session_id, branch_id, created_at, priority, serial, label, next_state, next_created_at, transition, code, sm_transition, reason,
id, ticket_id, state, branch_session_id, service_id, counter_id, user_id, session_id, branch_id, created_at, priority, serial, label, next_state, next_created_at, transition, code, sm_transition, reason, source, jsonb_build_object($1, th.service->>$2, $3, th.service->>$4, $5, th.service->>$6) AS service, jsonb_build_object($7, th.counter->>$8, $9, th.counter->>$10) AS counter
FROM ticket_history as th
WHERE space_id = $11
AND state not in ($12)
AND first_created_at > CURRENT_DATE - interval $13
AND id > $14 order by id asc limit $15
Details
CREATE INDEX CONCURRENTLY ON ticket_history (space_id, id)
Rows: 31663148
Row progression: 31663148, 445960, 44596
Row estimates
- space_id (=): 445960
- id (>): 3166315
- first_created_at (>): 3166318
- state (<>): 27139841
- id (sort): 1
Existing indexes
- id PRIMARY
- branch_session_id, counter_id
- branch_session_id, service_id
- branch_session_id, state
- branch_session_id, user_id
- session_id, branch_session_id
- space_id
- space_id, state, branch_id, created_at, user_id
- state
- state, channel WHERE (counter_id IS NOT NULL) AND (service_id IS NOT NULL)
- ticket_id
|
|
32 min
7%
|
55 ms
|
34,917
postgres
|
SELECT COUNT(*) OVER(),
MAX(id) OVER() as maxid,
MIN(id) OVER() as minid,
MAX(created_at) OVER() as maxdate,
MIN(created_at) OVER() as mindate,
id, state, service_id, counter_id, user_id, branch_id, branch_session_id, session_id, appointment_id, priority, source, serial, label, code, reason, form, tz, path, stats, evaluate_at, transition, sm_transition, created_at,
id, state, service_id, jsonb_build_object($1, th.service->>$2, $3, th.service->>$4, $5, th.service->>$6, $7, th.service->>$8) AS service, counter_id, jsonb_build_object($9, th.counter->>$10, $11, th.counter->>$12, $13, th.counter->>$14, $15, th.counter->>$16) AS counter, user_id, jsonb_build_object($17, th.user->>$18, $19, th.user->>$20, $21, th.user->>$22) AS user, branch_id, jsonb_build_object($23, th.branch->>$24, $25, th.branch->>$26, $27, th.branch->>$28) AS branch, branch_session_id, session_id, appointment_id, priority, source, serial, label, code, reason, form, tz, path, stats, evaluate_at, transition, sm_transition, created_at
FROM ticket_history as th
WHERE space_id = $29
AND id > $30 order by id asc limit $31
Details
CREATE INDEX CONCURRENTLY ON ticket_history (space_id, id)
Rows: 31663148
Row progression: 31663148, 445960, 44596
Row estimates
- space_id (=): 445960
- id (>): 3166315
- id (sort): 1
Existing indexes
- id PRIMARY
- branch_session_id, counter_id
- branch_session_id, service_id
- branch_session_id, state
- branch_session_id, user_id
- session_id, branch_session_id
- space_id
- space_id, state, branch_id, created_at, user_id
- state
- state, channel WHERE (counter_id IS NOT NULL) AND (service_id IS NOT NULL)
- ticket_id
|
|
24 min
6%
|
69 ms
|
21,141
postgres
|
-- tickets-stats-average
WITH DATA AS (
SELECT extract($2 from to_timestamp(lower(tr))::timestamptz at time zone $3) as val,
(keys->>$4)::integer AS branch_id,
jsonb_strip_nulls(jsonb_build_object(
$5,
round(sum((values->$6)::NUMERIC) FILTER (WHERE (values->$7)::numeric IS NOT NULL)
/
sum((values->$8)::NUMERIC) FILTER (WHERE (values->$9)::numeric IS NOT NULL), $10),
$11,
round(sum((values->$12)::NUMERIC) FILTER (WHERE (values->$13)::numeric IS NOT NULL)
/
sum((values->$14)::NUMERIC) FILTER (WHERE (values->$15)::numeric IS NOT NULL), $16)
)) AS unit_count
FROM ticket_stats
WHERE space_id = $1
AND (keys->>$17)::integer in ($18)
AND tr <@ numrange($19, $20)
GROUP BY val, branch_id
),
AGG_DATA AS (
SELECT b.name,
b.metadata->>$21 as color,
jsonb_agg(jsonb_build_object($22, d.val, $23, d.unit_count)) as unit_count,
$24 as time_interval
FROM DATA d
JOIN branches b ON d.branch_id = b.id
GROUP BY b.name, color
),
TOTALS AS (
SELECT $25 as name,
$26 as color,
jsonb_agg(jsonb_build_object($27, val, $28, unit_count)) as unit_count,
$29 as time_interval
FROM (
SELECT val,
jsonb_strip_nulls(jsonb_build_object(
$30, round(AVG((unit_count->>$31)::numeric), $32),
$33, round(AVG((unit_count->>$34)::numeric), $35)
)) AS unit_count
FROM DATA
GROUP BY val
) AS sub
)
SELECT jsonb_agg(data) as aggdata
FROM (
SELECT * FROM AGG_DATA
UNION ALL
SELECT * FROM TOTALS
) as data
|
|
9 min
2%
|
25 ms
|
21,135
postgres
|
-- influx-by-service-profile
WITH top_profiles AS (
select (service->>$3)::integer as id, count($4) as sp_tickets
from ticket_archive
where space_id = $1
and branch_id in ($5)
and created_at >= to_timestamp($6)
and created_at <= to_timestamp($7)
group by 1
order by 2 desc
),
data AS (
select
extract($8 from created_at at time zone $9) as val,
count($10) as cnt,
(service->>$11)::integer as service_profile_id
from ticket_archive
where space_id = $2
and created_at >= to_timestamp($12)
and created_at <= to_timestamp($13)
group by 3, 1
order by 2 desc, 3 asc
),
filtered_data AS (
SELECT *
FROM data
JOIN top_profiles
ON data.service_profile_id = top_profiles.id
),
service_profile2 AS (
SELECT filtered_data.cnt,
filtered_data.val,
service_profiles.id,
name,
code,
color
FROM filtered_data
INNER JOIN service_profiles
ON service_profiles.id = filtered_data.service_profile_id::int
ORDER BY filtered_data.cnt DESC
),
data3 AS (
SELECT id,
name,
color,
code,
$14 AS time_interval,
jsonb_agg(JSONB_BUILD_OBJECT($15, val, $16, cnt)) AS unit_count
FROM service_profile2
GROUP BY id, name, code, color
)
SELECT json_agg(data3)::jsonb as aggdata
FROM data3
|
|
6 min
1%
|
786 ms
|
487
postgres
|
--tickets-stats-total
WITH data1 AS (
SELECT COALESCE(sum((values->$4)::numeric), $5) AS total_count,
round(sum((values->$6)::numeric) / sum((values->$7)::numeric), $8) AS waiting_average,
round(sum((values->$9)::numeric) / sum((values->$10)::numeric), $11) AS attending_average
FROM ticket_stats
WHERE space_id = $1
AND (keys->>$12)::integer in ($13, $14, $15, $16, $17, $18, $19)
AND tr <@ numrange($20, $21)
),
data2 AS (
SELECT
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $22 = $23 ), $24)
AS waiting_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $25 = $26 ), $27)
AS attending_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $28 = $29), $30)
AS expired_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $31 = $32), $33)
AS cancelled_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $34 = $35), $36)
AS paused_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $37 = $38), $39)
AS terminated_count
FROM ticket_archive
LEFT JOIN LATERAL (select elem from jsonb_array_elements(ticket_archive.path) as elem) sub
ON ticket_archive.path IS NOT NULL AND ticket_archive.path != $40::jsonb
WHERE space_id = $2
AND branch_id in ($41, $42, $43, $44, $45, $46, $47)
-- AND EXTRACT(epoch FROM created_at)::numeric <@ numrange('1765756800', '1766361600')
AND created_at >= to_timestamp($48)
AND created_at < to_timestamp($49)
),
data3 AS (
select
coalesce(sum((stats->>$50)::integer), $51) as slot_noshow_count,
coalesce(sum((stats->>$52)::integer), $53) as slot_booked_count,
coalesce(sum((stats->>$54)::integer), $55) as slot_checked_count
from slot
where space_id = $3
and numrange(start_time_ts, end_time_ts) <@ numrange($56, $57)
)
select json_agg(data4)::jsonb as aggdata
from (
SELECT
data1.total_count,
data1.waiting_average,
data1.attending_average,
data2.waiting_count,
data2.attending_count,
data2.paused_count,
data2.terminated_count,
data2.expired_count,
data2.cancelled_count,
data3.slot_noshow_count,
data3.slot_booked_count,
data3.slot_checked_count
FROM data1, data2, data3
) data4
|
|
6 min
1%
|
807 ms
|
467
postgres
|
--tickets-stats-total
WITH data1 AS (
SELECT COALESCE(sum((values->$4)::numeric), $5) AS total_count,
round(sum((values->$6)::numeric) / sum((values->$7)::numeric), $8) AS waiting_average,
round(sum((values->$9)::numeric) / sum((values->$10)::numeric), $11) AS attending_average
FROM ticket_stats
WHERE space_id = $1
AND (keys->>$12)::integer in ($13, $14)
AND tr <@ numrange($15, $16)
),
data2 AS (
SELECT
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $17 = $18 and state = $19 ), $20)
AS waiting_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $21 = $22 and state = $23 ), $24)
AS attending_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $25 = $26), $27)
AS expired_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $28 = $29 and state = $30), $31)
AS cancelled_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $32 = $33 and state = $34), $35)
AS paused_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $36 = $37 and state = $38), $39)
AS terminated_count
FROM ticket_archive
LEFT JOIN LATERAL (select elem from jsonb_array_elements(ticket_archive.path) as elem) sub
ON ticket_archive.path IS NOT NULL AND ticket_archive.path != $40::jsonb
WHERE space_id = $2
AND branch_id in ($41, $42)
-- AND EXTRACT(epoch FROM created_at)::numeric <@ numrange('1766016000', '1766102400')
AND created_at >= to_timestamp($43)
AND created_at < to_timestamp($44)
),
data3 AS (
select
coalesce(sum((stats->>$45)::integer), $46) as slot_noshow_count,
coalesce(sum((stats->>$47)::integer), $48) as slot_booked_count,
coalesce(sum((stats->>$49)::integer), $50) as slot_checked_count
from slot
where space_id = $3
and numrange(start_time_ts, end_time_ts) <@ numrange($51, $52)
)
select json_agg(data4)::jsonb as aggdata
from (
SELECT
data1.total_count,
data1.waiting_average,
data1.attending_average,
data2.waiting_count,
data2.attending_count,
data2.paused_count,
data2.terminated_count,
data2.expired_count,
data2.cancelled_count,
data3.slot_noshow_count,
data3.slot_booked_count,
data3.slot_checked_count
FROM data1, data2, data3
) data4
|
|
4 min
1%
|
258 ms
|
1,030
postgres
|
--tickets-stats-total
WITH data1 AS (
SELECT COALESCE(sum((values->$4)::numeric), $5) AS total_count,
round(sum((values->$6)::numeric) / sum((values->$7)::numeric), $8) AS waiting_average,
round(sum((values->$9)::numeric) / sum((values->$10)::numeric), $11) AS attending_average
FROM ticket_stats
WHERE space_id = $1
AND (keys->>$12)::integer in ($13, $14, $15, $16, $17, $18, $19)
AND tr <@ numrange($20, $21)
),
data2 AS (
SELECT
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $22 = $23 and state = $24 ), $25)
AS waiting_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $26 = $27 and state = $28 ), $29)
AS attending_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $30 = $31), $32)
AS expired_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $33 = $34 and state = $35), $36)
AS cancelled_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $37 = $38 and state = $39), $40)
AS paused_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $41 = $42 and state = $43), $44)
AS terminated_count
FROM ticket_archive
LEFT JOIN LATERAL (select elem from jsonb_array_elements(ticket_archive.path) as elem) sub
ON ticket_archive.path IS NOT NULL AND ticket_archive.path != $45::jsonb
WHERE space_id = $2
AND branch_id in ($46, $47, $48, $49, $50, $51, $52)
-- AND EXTRACT(epoch FROM created_at)::numeric <@ numrange('1766016000', '1766102400')
AND created_at >= to_timestamp($53)
AND created_at < to_timestamp($54)
),
data3 AS (
select
coalesce(sum((stats->>$55)::integer), $56) as slot_noshow_count,
coalesce(sum((stats->>$57)::integer), $58) as slot_booked_count,
coalesce(sum((stats->>$59)::integer), $60) as slot_checked_count
from slot
where space_id = $3
and numrange(start_time_ts, end_time_ts) <@ numrange($61, $62)
)
select json_agg(data4)::jsonb as aggdata
from (
SELECT
data1.total_count,
data1.waiting_average,
data1.attending_average,
data2.waiting_count,
data2.attending_count,
data2.paused_count,
data2.terminated_count,
data2.expired_count,
data2.cancelled_count,
data3.slot_noshow_count,
data3.slot_booked_count,
data3.slot_checked_count
FROM data1, data2, data3
) data4
|
|
2 min
0.5%
|
157 ms
|
900
postgres
|
-- dataset/ticket-count-heatmap
WITH _tickets AS (
SELECT
id,
branch_id,
created_at AT TIME ZONE tz AS date
FROM ticket_archive
WHERE space_id = $1
AND (NULLIF($2, $7) IS NULL OR to_timestamp($3) < created_at)
AND (NULLIF($4, $8) IS NULL OR to_timestamp($5) > created_at)
AND branch_id = ANY($6)
),
_data AS (
SELECT
extract($9 from date_trunc($10, date)) as year,
extract($11 from date_trunc($12, date)) as month,
extract($13 from date_trunc($14, date)) as day,
extract($15 from date_trunc($16, date)) as hour,
COUNT($17) AS value
FROM _tickets
GROUP BY 1,2,3,4
)
SELECT JSON_AGG(_data) as aggdata FROM _data
|
|
2 min
0.5%
|
346 ms
|
379
postgres
|
-- count-tickets-stats
WITH data AS (
select branch_id, count($2) as cnt
from ticket_archive
where space_id = $1
and branch_id in ($3, $4, $5, $6, $7, $8, $9)
and created_at >= to_timestamp($10)
and created_at <= to_timestamp($11)
group by 1
),
data3 AS (
SELECT branches.id as id,
branches.name as name,
branches.metadata->>$12 as color,
data.cnt
FROM data
JOIN branches ON data.branch_id = branches.id
)
SELECT json_agg(data3) as aggdata FROM data3
|
|
2 min
0.5%
|
21 ms
|
6,319
postgres
|
-- count-tickets-by-user
with data AS (
SELECT user_id,
count(ticket_id) as cnt,
avg((stats->>$2)::numeric)::int as avg_attending_time
FROM ticket_history
WHERE space_id = $1
AND state = $3
AND branch_id in ($4)
AND created_at >= to_timestamp($5)
AND created_at <= to_timestamp($6)
AND user_id IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
),
data2 AS (
SELECT data.cnt,
users.id,
users.name,
data.avg_attending_time
FROM data
INNER JOIN users
ON data.user_id = users.id
ORDER BY data.cnt DESC
)
SELECT json_agg(data2)::jsonb as aggdata FROM data2
|
|
1 min
0.3%
|
69 ms
|
1,268
postgres
|
WITH _tickets AS (
SELECT id, branch_id, created_at AT TIME ZONE tz AS ts, (stats->>$7)::numeric as value
FROM ticket_archive
WHERE space_id = $1
AND stats ? $8
AND (NULLIF($2, $9) IS NULL OR to_timestamp($3) < created_at)
AND (NULLIF($4, $10) IS NULL OR to_timestamp($5) > created_at)
AND branch_id = ANY($6)
),
_data AS (
SELECT
extract($11 from date_trunc($12, ts)) as year,
extract($13 from date_trunc($14, ts)) as month,
extract($15 from date_trunc($16, ts)) as day,
extract($17 from date_trunc($18, ts)) as hour,
ROUND(AVG(value), $19) as value
FROM _tickets
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4
)
SELECT JSON_AGG(_data) as aggdata FROM _data
|
|
1 min
0.3%
|
114 ms
|
604
postgres
|
--tickets-stats-total
WITH data1 AS (
SELECT COALESCE(sum((values->$4)::numeric), $5) AS total_count,
round(sum((values->$6)::numeric) / sum((values->$7)::numeric), $8) AS waiting_average,
round(sum((values->$9)::numeric) / sum((values->$10)::numeric), $11) AS attending_average
FROM ticket_stats
WHERE space_id = $1
AND (keys->>$12)::integer in ($13, $14, $15, $16, $17, $18, $19, $20)
AND tr <@ numrange($21, $22)
),
data2 AS (
SELECT
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $23 = $24 and state = $25 ), $26)
AS waiting_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $27 = $28 and state = $29 ), $30)
AS attending_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $31 = $32), $33)
AS expired_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $34 = $35 and state = $36), $37)
AS cancelled_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $38 = $39 and state = $40), $41)
AS paused_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $42 = $43 and state = $44), $45)
AS terminated_count
FROM ticket_archive
LEFT JOIN LATERAL (select elem from jsonb_array_elements(ticket_archive.path) as elem) sub
ON ticket_archive.path IS NOT NULL AND ticket_archive.path != $46::jsonb
WHERE space_id = $2
AND branch_id in ($47, $48, $49, $50, $51, $52, $53, $54)
-- AND EXTRACT(epoch FROM created_at)::numeric <@ numrange('1766012400', '1766098800')
AND created_at >= to_timestamp($55)
AND created_at < to_timestamp($56)
),
data3 AS (
select
coalesce(sum((stats->>$57)::integer), $58) as slot_noshow_count,
coalesce(sum((stats->>$59)::integer), $60) as slot_booked_count,
coalesce(sum((stats->>$61)::integer), $62) as slot_checked_count
from slot
where space_id = $3
and numrange(start_time_ts, end_time_ts) <@ numrange($63, $64)
)
select json_agg(data4)::jsonb as aggdata
from (
SELECT
data1.total_count,
data1.waiting_average,
data1.attending_average,
data2.waiting_count,
data2.attending_count,
data2.paused_count,
data2.terminated_count,
data2.expired_count,
data2.cancelled_count,
data3.slot_noshow_count,
data3.slot_booked_count,
data3.slot_checked_count
FROM data1, data2, data3
) data4
|
|
1 min
0.2%
|
145 ms
|
398
postgres
|
-- dataset/ticket-count-heatmap-by-branch
WITH _tickets AS (
SELECT
id,
branch_id,
branch->>$7 as branch_name,
created_at AT TIME ZONE tz AS date
FROM ticket_archive
WHERE space_id = $1
AND (NULLIF($2, $8) IS NULL OR to_timestamp($3) < created_at)
AND (NULLIF($4, $9) IS NULL OR to_timestamp($5) > created_at)
AND branch_id = ANY($6)
),
_data AS (
SELECT
extract($10 from date_trunc($11, date)) as year,
extract($12 from date_trunc($13, date)) as month,
extract($14 from date_trunc($15, date)) as day,
branch_id,
branch_name,
COUNT($16) as value
FROM _tickets
GROUP BY 1,2,3,4,5
)
SELECT JSON_AGG(_data) as aggdata FROM _data
|
|
1 min
0.2%
|
80 ms
|
661
postgres
|
-- tickets-stats-average
WITH DATA AS (
SELECT extract($2 from to_timestamp(lower(tr))::timestamptz at time zone $3) as val,
(keys->>$4)::integer AS branch_id,
jsonb_strip_nulls(jsonb_build_object(
$5,
round(sum((values->$6)::NUMERIC) FILTER (WHERE (values->$7)::numeric IS NOT NULL)
/
sum((values->$8)::NUMERIC) FILTER (WHERE (values->$9)::numeric IS NOT NULL), $10),
$11,
round(sum((values->$12)::NUMERIC) FILTER (WHERE (values->$13)::numeric IS NOT NULL)
/
sum((values->$14)::NUMERIC) FILTER (WHERE (values->$15)::numeric IS NOT NULL), $16)
)) AS unit_count
FROM ticket_stats
WHERE space_id = $1
AND (keys->>$17)::integer in ($18, $19, $20)
AND tr <@ numrange($21, $22)
GROUP BY val, branch_id
),
AGG_DATA AS (
SELECT b.name,
b.metadata->>$23 as color,
jsonb_agg(jsonb_build_object($24, d.val, $25, d.unit_count)) as unit_count,
$26 as time_interval
FROM DATA d
JOIN branches b ON d.branch_id = b.id
GROUP BY b.name, color
),
TOTALS AS (
SELECT $27 as name,
$28 as color,
jsonb_agg(jsonb_build_object($29, val, $30, unit_count)) as unit_count,
$31 as time_interval
FROM (
SELECT val,
jsonb_strip_nulls(jsonb_build_object(
$32, round(AVG((unit_count->>$33)::numeric), $34),
$35, round(AVG((unit_count->>$36)::numeric), $37)
)) AS unit_count
FROM DATA
GROUP BY val
) AS sub
)
SELECT jsonb_agg(data) as aggdata
FROM (
SELECT * FROM AGG_DATA
UNION ALL
SELECT * FROM TOTALS
) as data
|
|
1 min
0.2%
|
157 ms
|
331
postgres
|
--tickets-states-stats
WITH data AS (
SELECT val,
count(*) AS cnt,
state
FROM (
SELECT created_at,
CASE
WHEN path IS NULL
THEN state
ELSE ((path->>$2)::jsonb)->>$3
END AS state,
extract($4 from created_at at time zone $5) as val
FROM ticket_archive
WHERE space_id = $1
AND branch_id in ($6, $7, $8)
AND created_at >= to_timestamp($9)
AND created_at <= to_timestamp($10)
) subquery
GROUP BY val, state
ORDER BY val
),
data2 AS (
SELECT jsonb_object_agg(state, cnt) AS tickets,
val
FROM data
GROUP BY val
),
data3 AS (
SELECT jsonb_agg(JSONB_BUILD_OBJECT($11, data2.val, $12, data2.tickets)) AS unit_count,
$13 as time_interval
FROM data2
)
SELECT json_agg(data3)::jsonb as aggdata FROM data3
|
|
1 min
0.2%
|
75 ms
|
622
postgres
|
select "tickets".* from "tickets" where "space_id" = $1 and "branch_id" in ($2) order by "created_at" desc limit $3
|
|
1 min
0.1%
|
113 ms
|
331
postgres
|
--tickets-stats-total
WITH data1 AS (
SELECT COALESCE(sum((values->$4)::numeric), $5) AS total_count,
round(sum((values->$6)::numeric) / sum((values->$7)::numeric), $8) AS waiting_average,
round(sum((values->$9)::numeric) / sum((values->$10)::numeric), $11) AS attending_average
FROM ticket_stats
WHERE space_id = $1
AND (keys->>$12)::integer in ($13, $14, $15)
AND tr <@ numrange($16, $17)
),
data2 AS (
SELECT
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $18 = $19 ), $20)
AS waiting_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $21 = $22 ), $23)
AS attending_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $24 = $25), $26)
AS expired_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $27 = $28), $29)
AS cancelled_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $30 = $31), $32)
AS paused_count,
COALESCE(COUNT(id) FILTER (WHERE sub.elem ->> $33 = $34), $35)
AS terminated_count
FROM ticket_archive
LEFT JOIN LATERAL (select elem from jsonb_array_elements(ticket_archive.path) as elem) sub
ON ticket_archive.path IS NOT NULL AND ticket_archive.path != $36::jsonb
WHERE space_id = $2
AND branch_id in ($37, $38, $39)
-- AND EXTRACT(epoch FROM created_at)::numeric <@ numrange('1765753200', '1766358000')
AND created_at >= to_timestamp($40)
AND created_at < to_timestamp($41)
),
data3 AS (
select
coalesce(sum((stats->>$42)::integer), $43) as slot_noshow_count,
coalesce(sum((stats->>$44)::integer), $45) as slot_booked_count,
coalesce(sum((stats->>$46)::integer), $47) as slot_checked_count
from slot
where space_id = $3
and numrange(start_time_ts, end_time_ts) <@ numrange($48, $49)
)
select json_agg(data4)::jsonb as aggdata
from (
SELECT
data1.total_count,
data1.waiting_average,
data1.attending_average,
data2.waiting_count,
data2.attending_count,
data2.paused_count,
data2.terminated_count,
data2.expired_count,
data2.cancelled_count,
data3.slot_noshow_count,
data3.slot_booked_count,
data3.slot_checked_count
FROM data1, data2, data3
) data4
|