I use MySQL 5.6.
What kind of SQL is common for RDBMS SQL that does not have an analysis function?
Please let me know if there is any SQL that can be resolved, even if it is not common.
Thank you for your cooperation.
What you want to do:
Below is the table.
Date and time the user ID was created
--------------------------
10012 2015-01-04
10010 2015-01-01
10011 2014-01-15
10010 2015-01-02
10011 2015-12-09
10010 2015-01-03
10012 2015-11-01
10011 2015-05-08
10012 2015-01-03
I would like to obtain the following results from SQL.
「Sort them in ascending order by User ID and Creation Date and Time, and
Numbering in groups (with same user ID)
User ID creation date and time group serial number
-------------------------------
10010 2015-01-01 1
10010 2015-01-02 2
10010 2015-01-03 3
10011 2014-01-15 1
10011 2015-05-08 2
10011 2015-12-09 3
10012 2015-01-03 1
10012 2015-01-04 2
10012 2015-11-01 3
In MySQL, serial numbers are given by user variables.
set@no:=0;
set@user_id:=null;
select
if(@user_id<>user_id,@no:=1,@no:=@no+1)as no,
@user_id: = user_id as user_id,
created_at
from order by user_id, created_at
If you don't want to use MySQL-specific features, would you like to use the following correlation subqueries?
select
t1.user_id, t1.created_at,(
select count(*) from t2
where1.user_id = t2.user_id
and t1.created_at>=t2.created_at
) as no
from tt1
order by
t1.user_id, t1.created_at
(Although the results will change slightly when there is an equivalent)
If the primary key in the table is id, it may be just a subquery as follows:
select
t1.user_id,
t1.created_at,
sum(t1.created_at>=t2.created_at)as no
from tt1 inner join t2 on t1.user_id = t2.user_id
group by t1.id, t1.user_id, t1.created_at
order by t1.user_id, no
ngyuki is posting
How do I use user variables?
MySQL documentation and
With AketiJyuuzou and yoku0825 of OracleACE
According to Meiji Kimura of Oracle Japan
The MySQL user variable has an undefined evaluation order.
http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98
Therefore, the results are not guaranteed.
© 2024 OneMinuteCode. All rights reserved.