基于商品维度的数据表条件计数实现方案咨询

First, let’s clarify the exact aggregation logic we need to apply per commodity (grouped by hs_code):

• countries: Number of distinct countries associated with the commodity.
• city: Comma-separated counts of distinct cities per country for the commodity. For example, apples have 1 city in Canada and 2 cities in the US → "1,2".
• company: Comma-separated counts of distinct companies per city for the commodity. For example, apples have 2 companies in Calgary, 1 in LA, and 1 in Chicago → "2,1,1".

Below are practical implementations using two common tools: SQL (for database-based data) and pandas (for Python-based data processing).

SQL Implementation #

We use common table expressions (CTEs) to compute intermediate counts, then aggregate those results into the final format. Syntax varies slightly by SQL dialect:

PostgreSQL / SQL Server #

WITH city_counts AS (
    SELECT 
        hs_code,
        country,
        COUNT(DISTINCT city) AS city_count
    FROM your_table_name
    GROUP BY hs_code, country
),
company_counts AS (
    SELECT 
        hs_code,
        city,
        COUNT(DISTINCT company) AS company_count
    FROM your_table_name
    GROUP BY hs_code, city
)
SELECT 
    t.hs_code,
    MAX(t.hs_name) AS hs_name, -- Extract the commodity name (apples/oranges)
    COUNT(DISTINCT t.country) AS countries,
    STRING_AGG(cc.city_count::TEXT, ',') AS city,
    STRING_AGG(cco.company_count::TEXT, ',') AS company
FROM your_table_name t
JOIN city_counts cc ON t.hs_code = cc.hs_code
JOIN company_counts cco ON t.hs_code = cco.hs_code
GROUP BY t.hs_code
ORDER BY t.hs_code;

MySQL #

Replace STRING_AGG with GROUP_CONCAT:

WITH city_counts AS (
    SELECT 
        hs_code,
        country,
        COUNT(DISTINCT city) AS city_count
    FROM your_table_name
    GROUP BY hs_code, country
),
company_counts AS (
    SELECT 
        hs_code,
        city,
        COUNT(DISTINCT company) AS company_count
    FROM your_table_name
    GROUP BY hs_code, city
)
SELECT 
    t.hs_code,
    MAX(t.hs_name) AS hs_name,
    COUNT(DISTINCT t.country) AS countries,
    GROUP_CONCAT(cc.city_count SEPARATOR ',') AS city,
    GROUP_CONCAT(cco.company_count SEPARATOR ',') AS company
FROM your_table_name t
JOIN city_counts cc ON t.hs_code = cc.hs_code
JOIN company_counts cco ON t.hs_code = cco.hs_code
GROUP BY t.hs_code
ORDER BY t.hs_code;

Python (Pandas) Implementation #

If you’re working with data in a pandas DataFrame, use groupby operations to compute the required aggregates:

import pandas as pd

# Sample data matching your input
data = [
    (1, 'apples', 'Canada', 'Calgary', 'West Jet'),
    (1, 'apples', 'Canada', 'Calgary', 'United'),
    (1, 'apples', 'US', 'Los Angeles', 'Alaska'),
    (1, 'apples', 'US', 'Chicago', 'Alaska'),
    (2, 'oranges', 'Korea', 'Seoul', 'West Jet'),
    (2, 'oranges', 'China', 'Shanghai', "John's Freight Co"),
    (2, 'oranges', 'China', 'Harbin', "John's Freight Co"),
    (2, 'oranges', 'China', 'Ningbo', "John's Freight Co"),
]

df = pd.DataFrame(data, columns=['hs_code', 'hs_name', 'country', 'city', 'company'])

# Step 1: Count distinct countries per commodity
country_count = df.groupby(['hs_code', 'hs_name'])['country'].nunique().reset_index(name='countries')

# Step 2: Count distinct cities per country, then aggregate to comma-separated string
city_per_country = df.groupby(['hs_code', 'country'])['city'].nunique().reset_index(name='city_count')
city_agg = city_per_country.groupby('hs_code')['city_count'].apply(lambda x: ','.join(map(str, x))).reset_index(name='city')

# Step3: Count distinct companies per city, then aggregate to comma-separated string
company_per_city = df.groupby(['hs_code', 'city'])['company'].nunique().reset_index(name='company_count')
company_agg = company_per_city.groupby('hs_code')['company_count'].apply(lambda x: ','.join(map(str, x))).reset_index(name='company')

# Merge all results into the final dataframe
final_result = country_count.merge(city_agg, on='hs_code').merge(company_agg, on='hs_code')

print(final_result)

Running this code will output exactly the desired table structure.

Notes #

• Adjust your_table_name in SQL queries to match your actual table name.
• If your hs_code directly includes the commodity name (like "1: apples"), modify the SQL/pandas logic to split or extract the name as needed.
• For large datasets, SQL implementations are generally more efficient, while pandas offers flexibility for ad-hoc data analysis.

内容的提问来源于stack exchange，提问作者plausibly_exogenous