解决了。附上结果,也许哪天也有朋友需要做通讯录数据的处理,可以参考一下。
select phoneNum, count(*) as cnt, callType, max(created) created
from (select t.*,
sum(case when prev_phonenum = phoneNum and prev_type = callType and prev_created=date(created) then 0 else 1 end) over (order by id) as grp
from (select t.*,
lag(callType) over (order by id) as prev_type,
lag(phonenum) over (order by id) as prev_phonenum,
lag(date(created)) over (order by id) as prev_created
from t
) t
) t
group by phoneNum, callType, grp
order by id desc limit 80 offset 0
1
orangeChu OP 为啥`md`的`table`语法没生效?
|
2
Iamnotfish 2019-08-08 22:27:20 +08:00 1
'''sqlite
select group_concat(phoneNum), callType, created from table group by callType ''' 抛砖引玉一下,看看大佬怎么回答。PS : V2 的 MD 语法不支持 TABLE |
3
orangeChu OP @Iamnotfish 感谢回复,(我还没试您给的答案,将在明天尝试一下)最后自己搞定了。
分享一下结果。(可能没有在描述中说明场景不是那么好搞,场景是这样的,记录来电记录,每次来电插入记录到数据库。展示数据时,按倒序来显示。上下两条号码相同时,根据 callType 来决定是否展示新的一行数据;不同号码时,直接展示该数据。展示结果类似手机上的通话记录~) ``` select phonenum, count(*) as cnt, type, max(created) from (select t.*, row_number() over (partition by phonenum order by id) as seqnum, row_number() over (partition by phonenum, type order by id) as seqnum_t from t ) t group by phonenum, type, (seqnum - seqnum_t); ``` |
4
orangeChu OP @Iamnotfish 尝试了一下,得到的结果好像不是我想要的。
--- 补充一下原来的题目: data | id | phoneNum | callType | created | | --- | ----------- | -------- | ---------------- | | 1 | 15000000000 | 1 | 2019-08-07 01:02 | | 2 | 15000000000 | 1 | 2019-08-07 02:03 | | 3 | 15000000000 | 2 | 2019-08-07 04:05 | | 4 | 15000000000 | 1 | 2019-08-07 05:07 | | 5 | 15000000000 | 2 | 2019-08-07 06:07 | | 6 | 15000000000 | 1 | 2019-08-07 06:20 | | 7 | 15000000000 | 1 | 2019-08-07 06:30 | | 8 | 15000000001 | 1 | 2019-08-07 07:07 | | 9 | 15000000000 | 1 | 2019-08-07 08:07 | | 10 | 15000000000 | 1 | 2019-08-08 08:07 | result | phoneNum | callType | created | count | | -------------- | -------- | ---------------- | ----- | | 15000000000 | 1 | 2019-08-07 02:03 | 2 | | 15000000000 | 2 | 2019-08-07 04:05 | 1 | | 15000000000 | 1 | 2019-08-07 05:07 | 1 | | 15000000000 | 2 | 2019-08-07 06:07 | 1 | | 15000000000 | 1 | 2019-08-07 06:30 | 2 | | 15000000001 | 1 | 2019-08-07 07:07 | 1 | | 15000000000 | 1 | 2019-08-07 08:07 | 1 | | 15000000000 | 1 | 2019-08-08 08:07 | 1 | |