UniqueID | MobileNumber | createDate
-----------+---------+-----+------------+-----------
U_23121 | 987654 | 2013-02-05
U_23124 | 987654 | 2013-02-02
U_23122 | 845263 | 2013-01-18
U_23128 | 654789 | 2013-01-16
U_23123 | 735689 | 2013-01-12
U_23128 | 654789 | 2013-01-11
U_23128 | 654789 | 2013-01-10
U_23126 | 987654 | 2013-01-09
U_23125 | 845263 | 2013-01-07
U_23126 | 845263 | 2013-01-06
U_23125 | 987654 | 2013-01-05
我想用手机号码记录过滤,如果不止一个继续根据createdDate获取最新信息,并像一样获取计数
UniqueID | Mobile_Number | createDate | count
-----------+---------+-----+------------+-----------
U_23121 | 987654 | 2013-02-05 | 2
U_23122 | 845263 | 2013-01-18 | 1
U_23128 | 654789 | 2013-01-16 | 1
U_23123 | 735689 | 2013-01-12 | 1
U_23128 | 654789 | 2013-01-11 | 2
U_23126 | 987654 | 2013-01-09 | 1
U_23125 | 845263 | 2013-01-07 | 2
U_23125 | 987654 | 2013-01-05 | 1
我会从下面的查询中得到需要的记录,但不会得到计数
SELECT te.*
FROM tableName as te
WHERE te.Mobile_Number <> (select Mobile_Number
from tableName
where createDate > te.createDate
limit 1
)
ORDER BY te.createDate DESC
这是一个缺口和孤岛问题。一种解决方案是为每一行分配一个"grp",然后按该组进行聚合。
您可以通过计算每行(直到该行)中与移动电话号码不同的移动电话号码的数量来分配grp。这是相邻手机号码的常数值。
结果查询:
SELECT MAX(UniqueId), MobileNumber,
MAX(createDate), COUNT(*)
FROM (SELECT te.*,
(SELECT COUNT(*)
FROM tableName te2
WHERE te2.createDate < te.createDate AND
te2.MobileNumber <> te.MobileNumber
) as grp
FROM tableName te
) te
GROUP BY MobileNumber, grp;
ORDER BY MIN(tcreateDate) DESC
好的,这是缺口和孤岛问题。如果Sqllite支持row_number
函数(3.25及更高版本),则可以使用以下方法
select MobileNumber, max(createDate), count(*)
from
(
select *,
row_number() over (order by createDate) -
row_number() over (partition by MobileNumber order by createDate) grp
from data
) t
group by grp, MobileNumber
通过使用组
SELECT te.* FROM tableName as te where te.Mobile_Number != (select Mobile_Number from tableName where createDate > te.createDate limit 1) GROUP BY Mobile_Number ORDER BY te.createDate DESC