MySQL -添加标志列来标识第一次付款

Question

我想改进我当前的查询。我有一个叫做Incomes的表。这里有一个sourceIdvarchar字段。对于我需要的字段，我有一个SELECT，但是我需要添加一个名为isFirstTime的额外字段来表示它是否第一次在行上使用sourceId。这是我当前的查询:

SELECT DISTINCT 
`income`.*,
CASE WHEN (
SELECT 
`income2`.id
FROM 
`income` as `income2`
WHERE
`income2`."sourceId" = `income`."sourceId"
ORDER BY 
`income2`.created asc
LIMIT 1
) = `income`.id THEN true ELSE false END
as isFirstIncome
FROM 
`income` as `income` 
WHERE `income`.incomeType IN ('passive', 'active') AND `income`.status = 'paid'
ORDER BY `income`.created desc
LIMIT 50

查询工作，但减慢如果我不断增加LIMIT或OFFSET。有什么建议吗?

更新1:增加了用于原始查询的WHERE语句

更新2:MYSQL版本5.7.22

Answer 1

可以使用有序解析函数来实现。

您可以使用ROW_NUMBER或RANK来获得所需的结果。

下面的查询将给出所需的输出。

SELECT *,
CASE
WHEN Row_number()
OVER(
PARTITION BY sourceid
ORDER BY created ASC) = 1 THEN true
ELSE false
END AS isFirstIncome
FROM   income 
WHERE incomeType IN ('passive', 'active') AND status = 'paid' 
ORDER BY created desc

DB Fiddle:查看这里的结果

Answer 2

我的第一个想法是isFirstIncome应该是表中的一个额外列。它应该在数据插入时填充。

如果你不喜欢，让我们试着优化查询…

让我们避免执行子查询超过50次。这需要将查询从内到外翻转。(这就像"爆炸-内爆"一样，查询会收集大量内容，然后对其进行排序，并将大多数行丢弃。)

总结:

1. 只需识别5行即可。

2. JOIN到任何需要的表(包括自己，如果合适的话);这是为了获得所需的任何其他列(包括isFirstIncome)。

   SELECT i3.*, 
   ( ... using i3 ... )  as isFirstIncome
   FROM (
   SELECT i1.id, i1.sourceId
   FROM  `income` AS i1
   WHERE  i1.incomeType IN ('passive', 'active')
   AND  i1.status = 'paid'
   ORDER BY  i1.created DESC
   LIMIT  50
   ) AS i2
   JOIN income AS i3  USING(id)
   ORDER BY i2.created DESC    -- yes, repeated
   

(我省略了isFirstIncome的计算;在其他答案中讨论过。但请注意，它最多将执行50次。)

(别名——i1, i2, i3——按照它们将被"使用"的顺序编号;(这是为了帮助执行SQL.)

辅助性能，添加

INDEX(status, incomeType, created, id, sourceId)

它应该对我的公式有帮助，但可能对其他版本没有帮助。您的版本将受益于

INDEX(sourceId, created, id)