参考这个:
select
row_number() over (partition by id order by id) idx,
*
from
(
select
1 as id,
*
from table_name
) a
赞0踩0评论0
回答了问题2016-06-22
如何将一张表的一列插入到另一张表的一列
Re如何将一张表的一列插入到另一张表的一列
我觉得不用这么麻烦,利用日期就可以自动生成一个从0-182的序列,参考
select
datediff(to_date( cast(ds as string),'yyyymmdd'), to_date('20150301','yyyymmdd'), 'dd' ) idx,
b.song_id
from
(
select
distinct ds,
1 as id
from user_actions
) a
join
(
select
distinct song_id,
1 as id
from user_actions
) b
on a.id = b.id
order by song_id, idx
limit 1000;
虽然很简单,但还是建议你学习sql,不然很吃亏
select
a.artist_id,
count(*) plays,
b.ds
from
mars_tianchi_songs_ori a
join
( select
*
from
mars_tianchi_user_actions_ori
where action_type = '1'
) b
on a.song_id = b.song_id
group by a.artist_id, b.ds