SQL Row_number()重复

By simon at 2018-02-28 • 0人收藏 • 57人看过

我在下面的一段代码中遇到了一些麻烦。我正在研究一个 我试图计数n的顶部篮子问题篮子数量 每个交易只能有4个项目的组合。下列 代码运行良好,但是当一个交易离子购买额外4项(或更多) 它不会被计算在combinedbasket步骤中。

With RowNums as (
select 
edw_transaction_id, row_number() over (partition by edw_transaction_id     order by article_name) as row_id, article_name
from thing1
 ),


 BasketItems as (
select a.edw_transaction_id, a.article_name as _1, b.article_name as_2,c.article_name as _3,d.article_name as _4
from (select edw_transaction_id,article_name from RowNums where row_id =1) a
join (select edw_transaction_id,article_name from RowNums where row_id =2) b
on a.edw_transaction_id = b.edw_transaction_id
join (select edw_transaction_id,article_name from RowNums where row_id =3) c
on a.edw_transaction_id = c.edw_transaction_id
join (select edw_transaction_id,article_name from RowNums where row_id =4) d
on a.edw_transaction_id = d.edw_transaction_id
),

combined_basket as (
select count(*) as basket_count, _1 as basket_item1,_2 as basket_item2,_3 as basket_item3,_4 as basket_item4
from BasketItems 
group by 2,3,4,5
order by 1 desc
)

select * 
from combined_basket
order by 1 desc
limit 10
BasketIt的输出ems看起来像这样:
Trans Id    Row_num     Article_Name
6368773827  1   Apples
6368773827  2   Oranges
6368773827  3   Cheese
6368773827  4   Egg
6368774403  1   Apple
6368774403  2   Egg
6368774403  3   Cake
6368774403  4   Salad
6368774403  5   Egg
6368774403  6   Apple
6368774403  7   Lemon
6368774403  8   Burger
从上面的代码中可以看出,它只会计算前4项和 离开其余(每笔交易)。有没有办法重复row
number 功能当它超过4或有另一种方式AR如此发现这个问题 每笔交易的4点数? 期望的结果是交易每个项目被标记为1到4。

2 个回复 | 最后更新于 2018-02-28
2018-02-28   #1

你可以添加一个数学函数,即Rownumber%4。这将解决这个问题 问题,即使没有分区: rownumber()结束(按articlename排序)%4作为rowid 尽管如此,我并不是100%确定MySQL是否存在%作为标志。如果没有, 寻找模块ar师。

2018-02-28   #2

你可以添加一个数学函数,即Rownumber%4。这将解决这个问题 问题,即使没有分区: rownumber()结束(按articlename排序)%4作为rowid 尽管如此,我并不是100%确定MySQL是否存在%作为标志。如果没有, 寻找模块ar师。

登录后方可回帖

Loading...