forked from mtusi2026/mtusi2026lab2
95 lines
2.2 KiB
SQL
95 lines
2.2 KiB
SQL
\set ON_ERROR_STOP on
|
|
\pset tuples_only on
|
|
\pset format unaligned
|
|
\pset pager off
|
|
|
|
WITH selected_messages AS (
|
|
(
|
|
SELECT
|
|
id::text AS id,
|
|
chat_id::text AS chat_id,
|
|
sender_id::text AS sender_id,
|
|
md5(text) AS text_md5,
|
|
to_char(
|
|
created_at AT TIME ZONE 'UTC',
|
|
'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'
|
|
) AS created_at
|
|
FROM messages
|
|
ORDER BY created_at, id
|
|
LIMIT 1
|
|
)
|
|
UNION ALL
|
|
(
|
|
SELECT
|
|
id::text,
|
|
chat_id::text,
|
|
sender_id::text,
|
|
md5(text),
|
|
to_char(
|
|
created_at AT TIME ZONE 'UTC',
|
|
'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'
|
|
)
|
|
FROM messages
|
|
ORDER BY created_at, id
|
|
OFFSET 49999
|
|
LIMIT 1
|
|
)
|
|
UNION ALL
|
|
(
|
|
SELECT
|
|
id::text,
|
|
chat_id::text,
|
|
sender_id::text,
|
|
md5(text),
|
|
to_char(
|
|
created_at AT TIME ZONE 'UTC',
|
|
'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'
|
|
)
|
|
FROM messages
|
|
ORDER BY created_at, id
|
|
OFFSET 99999
|
|
LIMIT 1
|
|
)
|
|
),
|
|
distribution AS (
|
|
SELECT
|
|
chat_id::text AS chat_id,
|
|
count(*) AS message_count
|
|
FROM messages
|
|
GROUP BY chat_id
|
|
)
|
|
SELECT jsonb_build_object(
|
|
'counts',
|
|
jsonb_build_object(
|
|
'users', (SELECT count(*) FROM users),
|
|
'chats', (SELECT count(*) FROM chats),
|
|
'chat_members', (SELECT count(*) FROM chat_members),
|
|
'messages', (SELECT count(*) FROM messages),
|
|
'sessions', (SELECT count(*) FROM sessions)
|
|
),
|
|
'message_distribution',
|
|
(
|
|
SELECT jsonb_object_agg(
|
|
chat_id,
|
|
message_count
|
|
ORDER BY chat_id
|
|
)
|
|
FROM distribution
|
|
),
|
|
'selected_messages',
|
|
(
|
|
SELECT jsonb_agg(
|
|
to_jsonb(selected_messages)
|
|
ORDER BY created_at, id
|
|
)
|
|
FROM selected_messages
|
|
),
|
|
'message_indexes',
|
|
(
|
|
SELECT jsonb_agg(indexname ORDER BY indexname)
|
|
FROM pg_indexes
|
|
WHERE schemaname = 'public'
|
|
AND tablename = 'messages'
|
|
)
|
|
)::text;
|