英文:
create new columns based on max values snowflake
问题
Here is the translated code part:
我有一个餐厅销售数据表。我想要确定每家店铺的最常见销售项目，并基于多个最大值创建新列。
我的表格如下：
| 店铺编号 | 周六销售数量 | 周日销售数量 | 菜单项目 |
| -------- | ------------ | ------------ | ------- |
| X123 | 125 | 120 | 馅饼 |
| X123 | 155 | 100 | 芝士蛋糕 |
| X123 | 100 | 125 | 布朗尼 |
| X124 | 178 | 77 | 意大利面 |
| X124 | 90 | 10 | 冰淇淋 |
| X124 | 90 | 15 | 披萨 |
| .... | .... | .... | .... |
我希望我的数据看起来像下面这样 - 每家店铺只有一行：
| 店铺编号 | 周六项目 | 周日项目 |
| -------- | ---------- | ---------- |
| X123 | 芝士蛋糕 | 布朗尼 |
| X124 | 意大利面 | 意大利面 |
新的列 '周六项目' 和 '周日项目' 包含在那一天销售数量最多的菜单项目。
我尝试过以下方法：
```sql
select distinct store_id,
case when max(Units_sold_Saturday) then Menu_item end as 'SaturdayItem',
case when max(Units_sold_Sunday) then Menu_item end as 'SundayItem'
from table
但我收到了 '意外的 'SaturdayItem' 错误。
我在想是否只需要创建一个空表格，列出所有唯一的店铺编号，然后创建新的空列 'Units_sold_Sunday' 和 'Units_sold_Saturday'。
我只是不确定如何获取菜单项目的值，而不是 Units_sold 值，因为这将是 max(Units_sold_Saturday) 的结果。
感谢您的帮助
Please note that translating code snippets may not always capture the full context, and you may need to adapt the code to your specific database system.
<details>
<summary>英文:</summary>
i have a table of restaurant sales. I want to identify which is the most common sale for each store and create new columns based on multiple max values.
My table is as follows:
| store_ id| Units_sold_Saturday | Units_sold_Sunday | Menu_Item |
| -------- | -------------- | -------------- |-------------- |
| X123 | 125| 120 | Pie |
| X123 | 155| 100| Cheesecake |
| X123 | 100| 125 | Brownie|
| X124 | 178 | 77| Pasta
| X124 | 90 | 10 | Ice cream
| X124 | 90 | 15 | Pizza
| ....| ....| ....| ....|
I want my data to look like the following- having one line for each store.
| store_ id| SaturdayItem| SundayItem|
| -------- | -------------- | -------------- |
| X123 | Cheesecake| Brownie|
| X124 | Pasta | pasta |
where the new column 'SaturdayItem' and new column 'SundayItem' contains the menu_item which sold the most units on that day
ive tried approaches such as
select distinct store_id,
case when max(Units_sold_Saturday) then Menu_item end as 'SaturdayItem',
case when max(Units_sold_Sunday) then Menu_item end as 'SundayItem'
from table
but i get 'unexpected 'SaturdayItem' error.
Im wondering if i just need to create an empty table which lists all unique store_ids, create new empty columns 'Units_sold_Sunday' and 'Units_sold_Saturday'.
Im just unsure how i would grab the menu_item value rather than Units_sold value as this would be the result of max(Units_sold_Saturday).
thanks for your help
</details>
# 答案1
**得分**: 1
使用 [MAX_BY](https://docs.snowflake.com/en/sql-reference/functions/min_by) 函数：
```sql
SELECT store_id,
MAX_BY(Menu_Item, Units_sold_Saturday) AS SaturdayItem,
MAX_BY(Menu_Item, Units_sold_Sunday) AS Sunday
FROM tab
GROUP BY store_id
ORDER BY store_id;
对于输入：
CREATE OR REPLACE TABLE tab(store_id TEXT,
Units_sold_Saturday INT,
Units_sold_Sunday INT,
Menu_Item TEXT)
AS
SELECT 'X123', 125 ,120 ,'Pie'
UNION ALL SELECT 'X123', 155 ,100 ,'Cheesecake'
UNION ALL SELECT 'X123', 100 ,125 ,'Brownie'
UNION ALL SELECT 'X124', 178 ,77 ,'Pasta'
UNION ALL SELECT 'X124', 90 ,10 ,'Ice cream'
UNION ALL SELECT 'X124', 90 ,15 ,'Pizza';
输出：
英文:
Using MAX_BY:
SELECT store_id,
MAX_BY(Menu_Item,Units_sold_Saturday) AS SaturdayItem,
MAX_BY(Menu_Item,Units_sold_Sunday) AS Sunday
FROM tab
GROUP BY store_id
ORDER BY store_id;
For input:
CREATE OR REPLACE TABLE tab(store_id TEXT,
Units_sold_Saturday INT,
Units_sold_Sunday INT,
Menu_Item TEXT)
AS
SELECT 'X123', 125 ,120 ,'Pie'
UNION ALL SELECT 'X123', 155 ,100 ,'Cheesecake'
UNION ALL SELECT 'X123', 100 ,125 ,'Brownie'
UNION ALL SELECT 'X124', 178 ,77 ,'Pasta'
UNION ALL SELECT 'X124', 90 ,10 ,'Ice cream'
UNION ALL SELECT 'X124', 90 ,15 ,'Pizza';
Output:
答案2
得分: 0
你可以使用公共表达式（CTEs）来创建这个逻辑。
我在类似的平台上实现了这个（在Databricks上使用SQL），这段代码可以帮助你。
你只需要更改表的名称。
with max_items as (
select
store_id,
max(units_sold_saturday) max_units_sold_saturday,
max(units_sold_sunday) max_units_sold_sunday
from
table
group by
store_id
)
select
m.store_id,
t_saturday.menu_item saturday_item,
t_sunday.menu_item sunday_item
from
max_items m
join table t_saturday on m.store_id = t_saturday.store_id
and m.max_units_sold_saturday = t_saturday.units_sold_saturday
join table t_sunday on m.store_id = t_sunday.store_id
and m.max_units_sold_sunday = t_sunday.units_sold_sunday
这是我的回复。
英文:
You can use CTEs to create this logic.
I implemented this in a similar platform (Databricks using SQL) and this code can help you.
You only need to change the name of the table
with max_items as (
select
store_id,
max(units_sold_saturday) max_units_sold_saturday,
max(units_sold_sunday) max_units_sold_sunday
from
table
group by
store_id
)
select
m.store_id,
t_saturday.menu_item saturday_item,
t_sunday.menu_item sunday_item
from
max_items m
join table t_saturday on m.store_id = t_saturday.store_id
and m.max_units_sold_saturday = t_saturday.units_sold_saturday
join table t_sunday on m.store_id = t_sunday.store_id
and m.max_units_sold_sunday = t_sunday.units_sold_sunday
Here is my return.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论