SQL:如何计算表中类别中每个项目的数量

Question

我有一张表，显示商店名称和订单来自的各个城市，取自 PostgreSQL 中的 json 查询。它基于一项调查，在调查中我们还可以看到该调查被查看了多少次以及有多少回复。我正在尝试在表中创建另一列，该列基本上计算每个城市每个商店的订单数量。此处显示了一个示例列表，其中商店名称在左侧，城市在右侧:

 1. Glossier   |   New York       |    7    |    6
 2. Glossier   |   New York       |    7    |    6
 3. Glossier   |   Chicago        |    7    |    6
 4. Glossier   |   Boston         |    7    |    6
 5. Glossier   |   New York       |    7    |    6
 6. Glossier   |   Chicago        |    7    |    6
 7. Sephora    |   New York       |    10   |    8
 8. Sephora    |   Baltimore      |    10   |    8
 9. Sephora    |   New York       |    10   |    8
 10. Sephora   |   Boston         |    10   |    8
 11. Sephora   |   New York       |    10   |    8
 12. Sephora   |   Baltimore      |    10   |    8
 13. Sephora   |   Chicago        |    10   |    8
 14. Sephora   |   San Francisco  |    10   |    8

我一直在使用的代码是:

WITH
    cities AS (
        SELECT context->>'city' AS city,
        context->>'shop_id' AS shopid
        FROM public.analytics_events
        ),
    views AS (
        SELECT
            DISTINCT ON (ref_id) ref_id,
            CASE WHEN context->>'order_total' IS NULL THEN 0
            ELSE cast(context->>'order_total' AS NUMERIC)
            END AS total,
                context->>'shop_id' AS shop_id
        FROM public.analytics_events
        ),
    view_counts AS (
            SELECT COUNT(DISTINCT ref_id) AS views, SUM(total)
            AS volume, shop_id
            FROM views
            GROUP BY shop_id
        ),
    response_counts AS (
            SELECT COUNT(survey_responses.id) AS responses,
            surveys.shop_id AS shop_id, surveys.question, 
            surveys.id AS surveyid
            FROM public.survey_responses
            LEFT JOIN public.surveys
            ON surveys.id = survey_responses.survey_id
            GROUP BY surveys.shop_id, surveys.question, surveyid
        )
    SELECT
        name,
        CASE WHEN views IS NULL THEN 0 ELSE views END,
        CASE WHEN responses IS NULL THEN 0 ELSE responses END,
        city,
        COUNT(*)
    FROM public.shops
    LEFT JOIN view_counts ON shops.id = view_counts.shop_id
    LEFT JOIN response_counts ON shops.id = response_counts.shop_id
    LEFT JOIN cities on shops.id = cities.shopid
    GROUP BY name, city;

但是，我现在卡在如何创建下一步上了。

Answer 1

我怀疑您只是想要group by:

WITH cities AS (
      SELECT context->>'city' AS city, context->>'shop_id' AS shopid
      FROM analytics
     )
SELECT name, city, COUNT(*)
FROM shops LEFT JOIN
     cities 
     ON shops.id = cities.shopid
GROUP BY name, city;