本文是小编为大家收集整理的关于SQL查找和组连续数，无重复的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
所以我有一个这样的桌子:
Taxi Client Time Tom A 1 Tom A 2 Tom B 3 Tom A 4 Tom A 5 Tom A 6 Tom B 7 Tom B 8 Bob A 1 Bob A 2 Bob A 3
，预期的结果将是这样:
Tom 3 Bob 1
我已经使用了分区函数来计算连续值，但结果变成了:
Tom A 2 Tom A 3 Tom B 2 Bob A 2
请帮忙，我的英语不好，谢谢！
推荐答案
这是差距和岛问题的变体.您可以使用窗口函数来解决它:
select taxi, count(*)
from (select t.taxi, t.client, count(*) as num_times
from (select t.*,
row_number() over (partition by taxi order by time) as seqnum,
row_number() over (partition by taxi, client order by time) as seqnum_c
from t
) t
group by t.taxi, t.client, (seqnum - seqnum_c)
having count(*) >= 2
)
group by taxi;
其他推荐答案
使用独特的计数
select taxi ,count( distinct cient) from table_name group by taxi
看来您的预期输出是错误的
其他推荐答案
我看不到您从哪里获得数字3.如果您只需连续订单来完成您的问题所说的问题，然后按客户端按照客户的数量进行分组，然后获得不同的组，我可以帮助您进行以下查询.鲍勃有1组，汤姆有4个.
出租车的划分，出租车订单，时间并检查此客户是否与此出租车的先前客户匹配.如果是，请不要计算此行.如果没有，请计算这一行，这是一个新组.
SELECT FEE.taxi,
SUM(FEE.clientNotSameAsPreviousInSequence)
FROM
(
SELECT taxi,
CASE
WHEN PreviousClient IS NULL THEN
1
WHEN PreviousClient <> client THEN
1
ELSE
0
END AS clientNotSameAsPreviousInSequence
FROM
(
SELECT *,
LAG(client) OVER (PARTITION BY taxi ORDER BY taxi, time) AS PreviousClient
FROM table
) taxisWithPreviousClient
) FEE
GROUP BY FEE.taxi;
问题描述
So I have a table like this:
Taxi Client Time Tom A 1 Tom A 2 Tom B 3 Tom A 4 Tom A 5 Tom A 6 Tom B 7 Tom B 8 Bob A 1 Bob A 2 Bob A 3
and the expected result will be like this:
Tom 3 Bob 1
I have used the partition function to count the consecutive value but the result become this:
Tom A 2 Tom A 3 Tom B 2 Bob A 2
Please help, I am not good in English, thanks!
推荐答案
This is a variation of a gaps-and-islands problem. You can solve it using window functions:
select taxi, count(*)
from (select t.taxi, t.client, count(*) as num_times
from (select t.*,
row_number() over (partition by taxi order by time) as seqnum,
row_number() over (partition by taxi, client order by time) as seqnum_c
from t
) t
group by t.taxi, t.client, (seqnum - seqnum_c)
having count(*) >= 2
)
group by taxi;
其他推荐答案
use distinct count
select taxi ,count( distinct cient) from table_name group by taxi
It seems your expected output is wrong
其他推荐答案
I don't see where you get the number 3 from. If you're trying to do what your question says and group by client in consecutive order only and then get the number of different groups, I can help you out with the following query. Bob has 1 group and Tom has 4.
Partition by taxi, ORDER BY taxi, time and check if this client matches the previous client for this taxi. If yes, do not count this row. If no, count this row, this is a new group.
SELECT FEE.taxi,
SUM(FEE.clientNotSameAsPreviousInSequence)
FROM
(
SELECT taxi,
CASE
WHEN PreviousClient IS NULL THEN
1
WHEN PreviousClient <> client THEN
1
ELSE
0
END AS clientNotSameAsPreviousInSequence
FROM
(
SELECT *,
LAG(client) OVER (PARTITION BY taxi ORDER BY taxi, time) AS PreviousClient
FROM table
) taxisWithPreviousClient
) FEE
GROUP BY FEE.taxi;
更多相关问答
联系站长