每周/每月/每年注册

数据与报告
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年6月 596 596 45307
2023年7月 517 1113 45307
2023年8月 583 1696 45890
2023年9月 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
10 个赞
3

您好，很抱歉我对这个工作原理如此迟钝，但当填入日期时，其中一个示例看起来是怎样的？

4

我不确定你的意思？如果那正是你想要的，那么每个选项下面都有一个结果示例。

1 个赞
5

明白了。

我想问的是：我应该在哪里以及以什么格式在代码中放置我的开始日期和结束日期？

6

你不需要，因为它应该会在运行查询按钮上方添加输入框（如果没有，保存后刷新它们就会显示），然后

我认为它会给你一个日期选择器。

2 个赞
7

是的，有一个已知问题，即参数输入框在第一次出现时不会显示，直到页面刷新后才会显示。

它还没有足够“swish”（流畅/完美）到可以有一个日期选择器（尽管我们可以期待 :slight_smile: :crossed_fingers:），但我认为它对格式相当宽容。我自己使用时会使用英国日期格式（例如 21/01/2024），但当我分享截图示例时，我会尽量坚持使用例如 2024-01-21，因为它更通用。

IMG_3563
IMG_35631179×1540 146 KB

3 个赞
8

明白了。谢谢。需要刷新页面。

1 个赞