英文:
Fill the rest of the group with the same value as row 1
问题
我已经添加了一个用于在列receipt_rank中包含1的行中填充'first buy store'的查询。
SELECT store,
customer,
receipt_rank,
CASE
WHEN receipt_rank = 1 THEN store
ELSE NULL
END AS first_buy_store
FROM table
但我希望在填充其他收据排名时也填充第一次购买的店铺。这需要按客户分组进行操作。
我现在有的：
| 店铺 | 客户 | 该客户的收据排名 | 第一次购买的店铺 |
|---|---|---|---|
| A | 1 | ||
| B | 1 | 1 | B |
| C | 1 | 2 | |
| B | 2 | ||
| B | 2 | 1 | B |
| C | 3 | 1 | C |
| A | 3 | ||
| A | 3 | 2 |
我想要的：
| 店铺 | 客户 | 该客户的收据排名 | 第一次购买的店铺 |
|---|---|---|---|
| A | 1 | ||
| B | 1 | 1 | B |
| C | 1 | 2 | B |
| B | 2 | ||
| B | 2 | 1 | B |
| C | 3 | 1 | C |
| A | 3 | ||
| A | 3 | 2 | C |
英文:
I have a database filled with customers and their visited stores. Some of them have done a purchase whereby the receipt number is also listed. I would like to label their 'first buy store' (where the ranking receipt is 1) and fill this for the rest of the customers' rows.
I have already added the 'first buy store' for rows that contain '1' in the column receipt_rank.
SELECT store,
customer,
receipt_rank,
case
when receipt_rank like '1' then store when receipt_rank > 1 then ??? else NULL
end as first_buy_store
FROM table
But I want to have the first buy store also filled in when the other receipt ranks are filled. This needs to be done/grouped per customer.
What I have now:
| Store | Customer | Receipt_rank (for this customer) | First buy store |
|---|---|---|---|
| A | 1 | ||
| B | 1 | 1 | B |
| C | 1 | 2 | |
| B | 2 | ||
| B | 2 | 1 | B |
| C | 3 | 1 | C |
| A | 3 | ||
| A | 3 | 2 |
What I would like to have:
| Store | Customer | Receipt_rank (for this customer) | First buy store |
|---|---|---|---|
| A | 1 | ||
| B | 1 | 1 | B |
| C | 1 | 2 | B |
| B | 2 | ||
| B | 2 | 1 | B |
| C | 3 | 1 | C |
| A | 3 | ||
| A | 3 | 2 | C |
答案1
得分: 0
你可以使用以下条件最大窗口函数：
select
store,
customer,
receipt_rank,
case
when Receipt_rank is not null
then max(case when Receipt_rank = 1 then store end) over (partition by customer)
else null
end as First_buy_store
from table_name
假设空的 Receipt_rank 值为 null。
英文:
You could use the conditional max window function as the following:
select
store,
customer,
receipt_rank,
case
when Receipt_rank is not null
then max(case when Receipt_rank = 1 then store end) over (partition by customer)
else null
end as First_buy_store
from table_name
Supposing that empty Receipt_rank values are nulls.
答案2
得分: 0
以下是已翻译的内容：
这将检索每位客户的相关店铺，当receipt_rank = 1时：
select customer, max(store) as store
from mytable
where receipt_rank = 1
group by customer
然后使用left join来获取期望的数据：
select t.store, t.customer, t.receipt_rank, s.store as 'First buy store'
from mytable t
left join (
select customer, max(store) as store
from mytable
where receipt_rank = 1
group by customer
) as s on s.customer = t.customer and receipt_rank >= 1
英文:
This will retrieve the related store for every customer when receipt_rank = 1:
select customer, max(store) as store
from mytable
where receipt_rank = 1
group by customer
Then use left join to get the expected data :
select t.store, t.customer, t.receipt_rank, s.store as 'First buy store'
from mytable t
left join (
select customer, max(store) as store
from mytable
where receipt_rank = 1
group by customer
) as s on s.customer = t.customer and receipt_rank >=1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论