时间戳

首页 > 解决方案 > IMPALA 查询他上次交易后最近 30 天的客户交易

sql - IMPALA 查询他上次交易后最近 30 天的客户交易

问题描述

我有 2 个表 CustomerInfo 和 CustomerTransaction

顾客信息

Name    CustID  Spouse          Address                 TransactionID
Noah    11  Michael Wilson  72 Oxford Street        1234567
Kim 8   Ed Thumpson 67 York Street          7245678
Viktor  3   Brian Carter    16 Darlinghurst Street  9873567
James   9   Helen Wright    59 Paul Street          2135678
Liam    5   Karen Collins   82 Newtown Park         2356789
Sandra  6   Karen Collins   82 Newtown Park         2137849

客户交易

TransactionID  LastTransactionTime      Type
2356789        8/01/19 11:50             Credit
2137849        7/21/19 09:30             Credit
1234567        8/02/19 11:50             Money
7245678        7/25/19 11:50             Credit
9873567        7/17/19 11:50             Credit
9873567        7/22/19 01:50             Credit
2137849        7/29/19 09:50             Credit
9873567        4/10/19 23:50             Credit
2137849        7/13/19 16:50             Credit
7245678         5/1/19 19:50             Credit

需要编写一个查询，我必须使用以下字段（TransactionCount）创建一个新表。它应该只显示从 LastTransactionTime 开始的最近 30 天的 count transactionCount。并且类型应该只是信用。

结果应该是：

CustID Type    TransactionID LastTransactionTime  TransactionCount
5      Credit  2356789       8/01/19 11:50              1
6      Credit  2137849       7/29/19 09:50              3
8      Credit  7245678       7/25/19 11:50              1
3      Credit  9873567       7/22/19 01:50              2

标签： sqlimpala

解决方案

尝试按客户和交易汇总过去 30 天内发生的交易以及仅涉及信用的交易。

SELECT
    ci.CustID,
    'Credit' AS Type,
    ci.TransactionID,
    MAX(ct.LastTransactionTime) AS LastTransactionTime,
    COUNT(*) AS TransactionCount
FROM CustomerInfo ci
INNER JOIN CustomerTransaction ct
    ON ci.TransactionID = ct.TransactionID
WHERE
    ct.LastTransactionTime > DATE_SUB(NOW(), 30) AND
    ct.Type = 'Credit'
GROUP BY
    ci.CustID,
    ci.TransactionID;

推荐阅读