Еженедельная/ежемесячная/ежегодная регистрация

Создание сообщества Данные и отчетность
1

:memo: Эти запросы предоставляют разбивку количества созданных новых учетных записей пользователей в указанный период времени, исключая подготовленные и неактивированные аккаунты. Итоги разбиты по количеству регистраций за каждую неделю/месяц/год, накопительному итогу за выбранный период и общему количеству на текущий момент, включающему все предыдущие регистрации.

ВНИМАНИЕ: Если пользователь удаляется, его записи больше не отображаются в базе данных и, следовательно, не будут включены в результаты запроса. Это может привести к расхождениям при сравнении последних запусков с предыдущими.

Еженедельные регистрации

-- [params]
-- date :start_date
-- date :end_date

WITH weekly_signups AS (

SELECT
    to_char(date_trunc('week', created_at)::date,'YYYY-MM-DD') AS week,
    COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
  AND staged = false
  AND active = true
GROUP BY week
),

all_to_date AS (

SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
  AND staged = false
  AND active = true

)

SELECT
    week AS "Начало недели", 
    signups AS "Регистрации за неделю", 
    SUM(signups::int) OVER (ORDER BY week) AS "Накопительный итог",
    (SUM(signups::int) OVER (ORDER BY week) + previous::int) AS "Общий итог"
FROM weekly_signups, all_to_date
ORDER BY week ASC
Начало недели Регистрации за неделю Накопительный итог Общий итог
2023-07-31 98 98 45418
2023-08-07 141 239 45559
2023-08-14 129 368 45688
2023-08-21 126 494 45814
2023-08-28 138 632 45952
2023-09-04 53 685 46005

Ежемесячные регистрации

-- [params]
-- date :start_date
-- date :end_date

WITH monthly_signups AS (
SELECT
    date_trunc('month', created_at)::date AS month,
    to_char(date_trunc('month', created_at)::date,'Month YYYY') AS month_display,
    COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
  AND staged = false
  AND active = true
GROUP BY month, month_display
),

all_to_date AS (
SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
  AND staged = false
  AND active = true
)

SELECT
    month_display AS "Месяц", 
    signups AS "Регистрации за месяц", 
    SUM(signups::int) OVER (ORDER BY month) AS "Накопительный итог",
    (SUM(signups::int) OVER (ORDER BY month) + previous::int) AS "Общий итог"
FROM monthly_signups, all_to_date
ORDER BY month
Месяц Регистрации за месяц Накопительный итог Общий итог
Июнь 2023 596 596 44790
Июль 2023 517 1113 45307
Август 2023 583 1696 45890
Сентябрь 2023 102 1798 46005

Ежегодные регистрации

-- [params]
-- date :start_date
-- date :end_date

WITH yearly_signups AS (

SELECT
    to_char(date_trunc('year', created_at)::date,'YYYY') AS year,
    COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
  AND staged = false
  AND active = true
GROUP BY year
),

all_to_date AS (

SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
  AND staged = false
  AND active = true

)

SELECT
    year AS "Год", 
    signups AS "Регистрации за год", 
    SUM(signups::int) OVER (ORDER BY year) AS "Накопительный итог",
    (SUM(signups::int) OVER (ORDER BY year) + previous::int) AS "Общий итог"
FROM yearly_signups, all_to_date
ORDER BY year ASC
Год Регистрации за год Накопительный итог Общий итог
2019 3590 3590 23135
2020 4258 7848 27393
2021 5908 13756 33301
2022 7889 21645 41190
2023 4815 26460 46005
3

Привет, извините за мою неосведомлённость в том, как это работает, но как выглядит один из этих примеров, когда даты заполнены?

4

Я не совсем понимаю, что вы имеете в виду? Под каждым пунктом есть пример результатов, если вы ищете именно это?

5

Понятно.

Вот что я пытаюсь спросить: где именно в коде мне нужно указать дату начала и дату окончания, и в каком формате их писать?

6

В коде это делать не нужно. Поля для ввода должны появиться над кнопкой запуска запроса (если их нет, обновите страницу после сохранения, и они отобразятся).

Думаю, вам просто предложат выбрать дату из календаря.

7

Ах, да. Известна проблема, когда поля ввода параметров не отображаются в первый раз до обновления страницы.

Пока ещё не совсем достаточно удобно для выбора даты (но будем надеяться :slight_smile: :crossed_fingers:), но, я думаю, формат ввода довольно гибкий. Я сам использую британский формат даты (например, 21/01/2024), но когда делюсь примерами в виде скриншотов, стараюсь придерживаться формата 2024-01-21, так как он более универсален.

IMG_3563
IMG_35631179×1540 146 KB

8

Теперь понятно. Спасибо. Нужно было обновить страницу.