时间戳

首页 > 解决方案 > 如何使分母成为 SQL 中同一行中每个数字的常数？

amazon-redshift - 如何使分母成为 SQL 中同一行中每个数字的常数？

问题描述

我正在尝试创建一个表格，其中平均销售额除以在某个月份注册帐户的一组用户，但是，我只能计算出除以在该月份进行购买的人数特定月份低于该群组的总金额。如何更改以下查询以使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

标签： amazon-redshiftperiscope

解决方案

您需要将同类群组的规模划分到其自己的子查询或 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
;

推荐阅读