mysql - 用另一列排序的列值对首次出现进行计数

我有一个包含以下列的assigns表：

id - int
id_lead - int
id_source - int
date_assigned - int (this represents a unix timestamp)

现在，假设这个表中有以下数据：

id id_lead id_source date_assigned
1  20      5         1462544612
2  20      6         1462544624
3  22      6         1462544615
4  22      5         1462544626
5  22      7         1462544632
6  25      6         1462544614
7  25      8         1462544621

现在，假设我想得到id_source为6的行的计数，并且是每个lead的第一个条目（按分配给asc的日期排序）。
因此在这种情况下，计数将为2，因为有2条导线（id_lead 22和25）的第一个id_源是6。
如何编写此查询，以使其快速并作为子查询select正常工作？我在想这样不管用的事：

select count(*) from `assigns` where `id_source`=6 order by `date_assigned` asc limit 1

我不知道如何以最佳方式编写此查询。任何帮助都将不胜感激。

最佳答案

伪码：

select rows
with a.id_source = 6
but only if
    there do not exist any row
        with same id_lead
        and smaller date_assigned

将其转换为SQL

select *                                     -- select rows
from assigns a
where a.id_source = 6                        -- with a.id_source = 6
  and not exists (                           -- but only if there do not exist any row
    select 1
    from assigns a1
    where a1.id_lead = a.id_lead             -- with same id_lead
      and a1.date_assigned < a.date_assigned -- and smaller date_assigned
  )

现在用select *替换select count(*)，您将得到您的结果。
http://sqlfiddle.com/#!9/3dc0f5/7
更新：
不存在的查询可以重写为排除左联接查询：

select count(*)
from assigns a
left join assigns a1
    on  a1.id_lead = a.id_lead
    and a1.date_assigned < a.date_assigned
where a.id_source = 6
  and a1.id_lead is null

如果要获取id_source的所有值的计数，以下查询可能是最快的：

select a.id_source, count(1)
from (
    select a1.id_lead, min(a1.date_assigned) date_assigned
    from assigns a1
    group by a1.id_lead
) a1
join assigns a
    on  a.id_lead = a1.id_lead
    and a.date_assigned = a1.date_assigned
group by a.id_source

您仍然可以用group by a.id_source替换where a.id_source = 6。
查询需要assigns(id_source)和assigns(id_lead, date_assigned)上的索引。

关于mysql - 用另一列排序的列值对首次出现进行计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37075169/