Unique grouping and count situation for rolling up to parent groupings-CodePudding

I am working on working on forming a logic:

user_id	city	county	state	country	region	is_perm
First	Oakland	Alameda	California	United States	North America	True
First	Fremont	Alameda	California	United States	North America	False
First	Broolyn	Kings	New York	United States	North America	True
Second	San Francisco	San Francisco	California	United States	North America	True
Second	Cleveland	Cuyahoga	Ohio	United States	North America	False

I am looking for output of following:

user_id	unique_city_count	unique_county_count	unique_state_count	unique_country	unique_region	perm_unique_city_count	perm_unique_county_count	perm_unique_state_count	perm_unique_country	perm_unique_region
First	3	2	2	1	1	2	2	2	1	1
Second	2	2	2	1	1	1	1	1	1	1

Column definitions:

user_id: User ID
unique_city_count: Unique City Counts
unique_county_count: Unique County Counts
unique_state_count: Unique State Counts
unique_country: Unique Country Counts
unique_region: Unique Region Counts
perm_unique_city_count: Unique City Counts who has a permanent home
perm_unique_county_count: Unique City Counts who has a permanent home
perm_unique_state_count: Unique City Counts who has a permanent home
perm_unique_country: Unique City Counts who has a permanent home
perm_unique_region: Unique City Counts who has a permanent home

Could anyone help me to achieve this?

Thanks a lot.

CodePudding user response：

Try the following options if the is_perm = true runs into error:

is_perm is true
is_perm = 'true'

select 
user_id,
count(distinct city) as unique_city_count,
count(distinct county) as unique_county_count,
count(distinct country) as unique_country_count,
count(distinct state) as unique_state_count,
count(distinct region) as unique_region_count,
count(distinct (case when is_perm = true then city end)) as perm_unique_city_count,
count(distinct (case when is_perm = true then county end)) as perm_unique_county_count
count(distinct (case when is_perm = true then country end)) perm_unique_country_count,
count(distinct (case when is_perm = true then state end)) as perm_unique_state_count,
count(distinct (case when is_perm = true then region end)) as perm_unique_region_count
from <table_name>
group by 1

CodePudding user response：

You didn't mention what database you're using, but this should work with most relational databases. There's a lot of COALESCE statements here, but those are necessary because one could only have perm (or non-perm) addresses, which could result in NULL values.

WITH perm AS (
    SELECT
        user_id,
        COUNT(DISTINCT city) perm_unique_city_count,
        COUNT(DISTINCT county) perm_unique_county_count,
        COUNT(DISTINCT state) perm_unique_state_count,
        COUNT(DISTINCT country) perm_unique_country,
        COUNT(DISTINCT region) perm_unique_region
    FROM
        table
    WHERE is_perm=True
    GROUP BY user_id
),
alldata AS (
    SELECT
        user_id,
        COUNT(DISTINCT city) unique_city_count,
        COUNT(DISTINCT county) unique_county_count,
        COUNT(DISTINCT state) unique_state_count,
        COUNT(DISTINCT country) unique_country,
        COUNT(DISTINCT region) unique_region
    FROM
        table
    GROUP BY user_id
)
SELECT
    COALESCE(perm.user_id,alldata.user_id) user_id,
    COALESCE(alldata.unique_city_count,0) unique_city_count,
    COALESCE(alldata.unique_county_count,0) unique_county_count,
    COALESCE(alldata.unique_state_count,0) unique_state_count,
    COALESCE(alldata.unique_country,0) unique_country,
    COALESCE(alldata.unique_region,0) unique_region,
    COALESCE(perm.perm_unique_city_count,0) perm_unique_city_count,
    COALESCE(perm.perm_unique_county_count,0) perm_unique_county_count,
    COALESCE(perm.perm_unique_state_count,0) perm_unique_state_count,
    COALESCE(perm.perm_unique_country,0) perm_unique_country,
    COALESCE(perm.perm_unique_region,0) perm_unique_region
FROM
    alldata
FULL OUTER JOIN
    perm
ON
    perm.user_id=alldata.user_id
ORDER BY
    COALESCE(perm.user_id,alldata.user_id);