hive写法:各个客户近1个月、3个月、6个月、12个月申请贷款笔数
时间: 2024-09-30 09:08:32 浏览: 18
在Hive中,为了统计每个客户近一个月、三个月、六个月和十二个月的申请贷款笔数,你需要结合日期操作和窗口函数。假设有一个名为`loan_applications`的表,其中包含`application_date`(申请日期)和`client_id`字段,你可以使用以下SQL查询:
```sql
WITH
-- 定义窗口范围
month_buckets AS (
SELECT
client_id,
application_date,
DATE_SUB(CURRENT_DATE(), INTERVAL n MONTH) AS bucket_start
FROM
loan_applications
CTE RECURSIVE
-- 迭代计算1个月到12个月
UNNEST([1, 3, 6, 12]) AS n
),
-- 计算每个客户的申请笔数
application_counts AS (
SELECT
client_id,
COUNT(*) AS application_count
FROM
month_buckets
GROUP BY
client_id, bucket_start
)
SELECT
client_id,
'近1个月' AS time_period, application_count,
'近3个月' AS time_period, (SELECT COUNT(*) FROM application_counts WHERE bucket_start >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)),
'近6个月' AS time_period, (SELECT COUNT(*) FROM application_counts WHERE bucket_start >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)),
'近12个月' AS time_period, (SELECT COUNT(*) FROM application_counts WHERE bucket_start >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH))
FROM
application_counts
ORDER BY
client_id, time_period;
```
这个查询首先创建了一个CTE(公共表表达式)来生成不同时间段的起始日期,然后按照每个时间段计数,并最后从`application_counts`中选择对应的笔数。