如何使分母成为 SQL 中同一行中每个数字的常量?

Question

我正在尝试创建一个表格，其中平均销售额除以在某个月份注册帐户的用户群，但是，我只能计算除以该特定月份进行购买的人数，这低于同期群的总金额。如何更改下面的查询以使每个avg_sucessful_transacted金额除以每月的同期群 0？

谢谢。

select sum (t.amount_in_dollars)/ count (distinct u.id)  as Avg_Successful_Transacted, (datediff(month,[u.created:month],[t.createdon:month])) as Cohort, [u.created:month] as Months,
count (distinct u.id) as Users
from [transaction_cache as t]
left join [user_cache as u] on t.owner = u.id
where t.type = 'savings' and t.status = 'successful' and [u.created:year] > ['2017-01-01':date:year]
group by cohort, months
order by Cohort, Months

Answer 1

您需要将同类群组大小分解为其自己的子查询或 CTE，以便计算在与同类群组的基准月匹配的当月创建的不同用户总数。

我通过使用date_trunc('Month', <date>, <date>)函数按创建用户的月份对用户进行分桶来解决这个问题，但您可能希望根据生成同类群组的特定业务逻辑以不同的方式处理它。

我不使用 Periscope，所以下面的示例查询是为纯 Redshift 构建的，但希望很容易将语法转换为 Periscope 的预期格式：

WITH cohort_sizes AS (
SELECT date_trunc('Month', created)::DATE AS cohort_month
, COUNT(DISTINCT(id)) AS cohort_size
FROM user_cache u
GROUP BY 1
),
cohort_transactions AS (
SELECT date_trunc('Month', created)::DATE AS cohort_month
, createdon
, owner
, type
, status
, amount_in_dollars
, id
, created
FROM transaction_cache t
LEFT JOIN user_cache u ON t.owner = u.id
WHERE t.type = 'savings'
AND t.status = 'successful'
AND u.created > '2017-01-01'
)
SELECT SUM(t.amount_in_dollars) / s.cohort_size AS Avg_Successful_Transacted
, (datediff(MONTH, u.created, t.createdon)) AS Cohort
, u.created                                                 AS Months
, count(DISTINCT u.id)                                      AS Users
FROM cohort_transactions t
JOIN cohort_sizes s ON t.cohort_month = s.cohort_month
LEFT JOIN user_cache AS u ON t.owner = u.id
GROUP BY s.cohort_size, Cohort, Months
ORDER BY Cohort, Months
;