-
-
Save yulisunny/cf1d505b5cdc91eb1c76139561dcaa83 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH persisted_rate AS ( | |
SELECT | |
date_trunc('minute', persisted_time) AS persisted_minute, | |
count(*) AS num_persisted | |
FROM persistent_queue_table | |
GROUP BY persisted_minute | |
), | |
polled_rate AS ( | |
SELECT | |
date_trunc('minute', poll_time) AS poll_minute, | |
count(*) AS num_polled | |
FROM persistent_queue_table | |
GROUP BY poll_minute | |
), | |
total_objects_persisted_by_minute AS ( | |
SELECT | |
persisted_minute, | |
( | |
SELECT sum(num_persisted) | |
FROM persisted_rate persisted_rate_2 | |
WHERE persisted_rate_2.persisted_minute <= persisted_rate_1.persisted_minute | |
) AS running_persisted_sum | |
FROM persisted_rate persisted_rate_1 | |
), | |
total_objects_polled_by_minute AS ( | |
SELECT | |
poll_minute, | |
( | |
SELECT sum(num_polled) | |
FROM polled_rate polled_rate_2 | |
WHERE polled_rate_2.poll_minute <= polled_rate_1.poll_minute | |
) AS running_polled_sum | |
FROM polled_rate polled_rate_1 | |
) | |
SELECT | |
persisted_minute AS minute, | |
(running_persisted_sum - running_polled_sum) AS queue_size | |
FROM total_objects_persisted_by_minute | |
INNER JOIN total_objects_polled_by_minute | |
ON persisted_minute = poll_minute | |
ORDER BY minute; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment