sql - 将我的查询从使用聚合函数转换为使用窗口函数
问题描述
我有一个表 ( wallet_credit_details
),其中每一行都是由单个客户进行的单个交易。还有另一个表 ( wallet_usage_details
) 显示了每个用户如何为这些交易付费。我想做的是 - 对于第一个表中的每一行(交易),客户从第二个表到第一个表中的交易日期的汇总(计数和总和)值。
例如,对于每笔交易,计算我们使用“充值”支付的同一客户之前的交易数量。
SELECT
wallet.customer_id,
wallet.credit_id AS transaction_id,
COALESCE(COUNT(usage.debit_id), 0) AS transactions_paid_count_to_date,
COALESCE(SUM(usage.used_amt_usd), 0) AS transactions_paid_amount_to_date,
COALESCE(COUNT(CASE WHEN usage.credit_title = 'Topups' AND usage.credit_type = 'Credit Card Topups' THEN 1 END), 0) AS topups_used_count_to_date,
COALESCE(SUM(CASE WHEN usage.credit_title = 'Topups' AND usage.credit_type = 'Credit Card Topups' THEN usage.used_amt_usd END), 0) AS topups_used_amount_to_date
FROM
prod_dwh.wallet_credit_details AS wallet
LEFT JOIN
prod_dwh.wallet_usage_details AS usage
ON
wallet.customer_id = usage.user_id
AND usage.transaction_date < wallet.day
WHERE
wallet.credit_title = 'Topups'
AND wallet.credit_type = 'Credit Card Topups'
AND wallet.day >= DATE '{min_date}'
AND wallet.day <= DATE '{max_date}'
GROUP BY
wallet.customer_id,
wallet.credit_id
如何使用窗口函数重现相同的逻辑?
解决方案
推荐阅读
- python - Creating a pandas pivot table to count number of times items appear in a list together
- c# - IOC Container Circular Dependency Between Service and Factory
- prolog - Prolog Infinite Loop: Bi-Directional Predicates
- c# - How to add a DLL to a C# program in Visual Studio Code?
- prolog - Convert DRS to Sentence
- javascript - How do I make a React prop only required when used in the context of a Container component with Typescript?
- mysql - 在 Connection.query (/var/www/html/node_modules/mysql/lib/Connection.js:198:25)
- javascript - 不和谐.js | UnhandledPromiseRejectionWarning:TypeError:无法读取未定义的属性“部分”
- django - 视图 ecommerceapp.views.CheckoutView 没有返回 HttpResponse 对象。它返回 None 而不是
- r - 如何修复这个 if_else 语句