将Postgresql转换为HiveSql

Question

如何将下面提到的postgresql查询转换为HiveSql

SELECT
COUNT(user_id) Total_profiles,
COUNT(user_id) FITLER (WHERE profile_date BETWEEN '2020-01-01' AND '2020-03-31') as Q1_profile,
COUNT(user_id) FITLER (WHERE profile_date BETWEEN '2020-04-01' AND '2020-06-30') as Q2_profile,
COUNT(user_id) FITLER (WHERE profile_date BETWEEN '2020-07-01' AND '2020-09-30') as Q3_profile,
COUNT(user_id) FITLER (WHERE profile_date BETWEEN '2020-010-01' AND '2020-12-31') as Q4_profile
FROM 
customer_profiles
WHERE 
profile_date BETWEEN '2020-01-01' AND '2020-12-31'

Answer 1

用例表达式:

SELECT
COUNT(user_id) Total_profiles,
COUNT(case when profile_date BETWEEN '2020-01-01' AND '2020-03-31' then user_id else null end) as Q1_profile,
COUNT(case when profile_date BETWEEN '2020-04-01' AND '2020-06-30' then user_id else null end) as Q2_profile,
COUNT(case when profile_date BETWEEN '2020-07-01' AND '2020-09-30' then user_id else null end) as Q3_profile,
COUNT(case when profile_date BETWEEN '2020-10-01' AND '2020-12-31' then user_id else null end) as Q4_profile
FROM 
customer_profiles
WHERE 
profile_date BETWEEN '2020-01-01' AND '2020-12-31'

同样在Hive版本>= 1.3你可以使用quarter函数:

SELECT
COUNT(user_id) Total_profiles,
COUNT(case when quarter(profile_date)=1 then user_id else null end) as Q1_profile,
COUNT(case when quarter(profile_date)=2 then user_id else null end) as Q2_profile,
COUNT(case when quarter(profile_date)=3 then user_id else null end) as Q3_profile,
COUNT(case when quarter(profile_date)=4 then user_id else null end) as Q4_profile
FROM 
customer_profiles
WHERE 
profile_date BETWEEN '2020-01-01' AND '2020-12-31'