sql - SQL:如何计算表中类别内每个项目的数量
问题描述
我有一个表格,显示商店的名称和订单来自的各个城市,取自 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;
但是,我现在坚持如何创建下一步。
解决方案
我怀疑你只是想要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;
推荐阅读
- java - Java Continue---我不明白我是如何进入无限循环的
- dictionary - 在 FOR 循环中向字典添加值。更新而不是“附加”
- python - 如何将 Faktory 与 ruby 作业和 python 工作者一起使用?
- java - java文件中UML类图的初始值大吗?
- c# - 在 C# 中,如何在数组中存储随机数等于某些总和的次数?
- javascript - 如何在 2 条机器人消息/命令之间放置一个计时器
- java - 为什么在进行 spring-security jdbc 身份验证时出现错误?
- java - 从 Jersey 1 迁移到 Jersey 2 - 如何取消注册在 WebTarget 添加的过滤器?
- reactjs - React - 不变式失败:浏览器历史记录需要 DOM
- excel - 使用 SpecialCells 进行自动筛选以删除行也删除标题行