时间戳

首页 > 解决方案 > 使用 row_number() 的 SQL 查询未返回预期输出

sql - 使用 row_number() 的 SQL 查询未返回预期输出

问题描述

我的目标是编写一个查询，该查询应返回产生最高平均值的城市。每个项目类别的销售额。

这是预期的输出：

item_category|city
books        |los_angeles
toys         |austin
electronics  |san_fransisco

我的 3 个表模式如下所示：

users
user_id|city

sales
user_id|item_id|sales_amt

items
item_id|item_category

这些是需要考虑的进一步注意事项：
 1. sales_amt 是唯一可能具有 Null 值的列。如果没有用户为特定的商品类别进行销售（销售中没有非空 sales_amt 的行），则城市名称应为 Null。
2. 每个不同的项目只有 1 行。超过 1 个城市符合条件，然后按字母顺序选择第一个。

我采取的尝试看起来像这样，但它没有产生正确的输出：

select a.item_category,a.city from (
select 
i.item_category,
u.city,
row_number() over (partition by i.item_category,u.city order by avg(s.sales_amt) desc)rk 
from sales s 
join users u on s.user_id=u.user_id 
join items i on i.item_id=s.item_id
group by i.item_category,u.city)a
where a.rk=1

我的输出没有返回针对 sales_amt 的 Null。此外，我得到非唯一的行。因此，我很紧张我没有正确地合并这两个音符。

我希望有人能帮帮忙。

标签： sql

解决方案

我的目标是编写一个查询，该查询应返回产生最高平均值的城市。每个项目类别的销售额。

这可以使用聚合和窗口函数来计算：

select ic.*
from (select i.item_category, u.city,
             row_number() over(partition by u.item_category order by avg(s.sales_amt) desc, u.city) as seqnum 
      from users u join
           sales s
           on s.user_id = u.user_id join
           items i
           on i.item_id = s.item_id
      group by i.item_category, u.city
     ) ic
where seqnum = 1;

您的问题明确表示“平均”，这就是为什么使用avg(). 但是，我怀疑您真的想要每个城市的总和，即sum().

笔记：

  • 你想要一排，row_number()而不是rank().
  • 您需要销售额来计算平均值，所以join，而不是left join。
  • 您需要每个item_category一行，以便用于分区。

推荐阅读