Quiero obtener recuentos de clientes únicos. Tengo una referencia de la consulta postgresql. ¿Podría convertir esta consulta en HiveSql?

SELECT
    COUNT(user_id) Total_profiles,
    COUNT(distinct user_id) FITLER (WHERE age BETWEEN 18 AND 12) as age_less_than_20
FROM 
    customer_profiles
WHERE 
    profile_date BETWEEN '2020-01-01' AND '2020-12-31'
0
balams 3 mar. 2021 a las 12:34

1 respuesta

La mejor respuesta

Expresiones de casos de uso:

SELECT
    COUNT(user_id) Total_profiles,
    COUNT(distinct case when age BETWEEN 18 AND 12 then user_id else null end) as age_less_than_20
FROM 
    customer_profiles
WHERE 
    profile_date BETWEEN '2020-01-01' AND '2020-12-31'

Un método más para contar distintos es el tamaño (collect_set ()):

SELECT
    COUNT(user_id) Total_profiles,
    size(collect_set(case when age BETWEEN 18 AND 12 then user_id else null end)) as age_less_than_20
FROM 
    customer_profiles
WHERE 
    profile_date BETWEEN '2020-01-01' AND '2020-12-31'
2
leftjoin 3 mar. 2021 a las 13:27