#повседневное

Вчерашний запрос, "заморозивший" миграцию на три часа:

SELECT entity_id, MAX(start_date), MAX(end_date)
FROM (
  SELECT entity_id, MAX(start_date) start_date, NULL AS end_date FROM audit_log
  WHERE attr = 'reviewed' AND newValue = 'true'
  GROUP BY entity_id
   UNION ALL
  SELECT entity_id, NULL AS start_date, MAX(end_date) end_date FROM audit_log
  WHERE attr = 'overriden' AND newValue = 'true'
  GROUP BY entity_id
) a GROUP BY entity_id; 

можно было бы написать сильно оптимальнее вот так:

SELECT entity_id, 
  MAX(if(attr = 'reviewed', NULL, start_date)) AS start_date,
    MAX(if(attr = 'reviewed', end_date, NULL)) end_date 
FROM audit_log
WHERE (attr = 'reviewed' OR attr='overriden') AND newValue = 'true'
GROUP BY entity_id;

При сильно похожих WHERE в таких конструкциях, удобнее схлопнуть UNION и "на лету" подставлять в SELECT нужное значение через if.

Developer's mind

#повседневное

Вчерашний запрос, "заморозивший" миграцию на три часа:

SELECT entity_id, MAX(start_date), MAX(end_date)
FROM (
  SELECT entity_id, MAX(start_date) start_date, NULL AS end_date FROM audit_log
  WHERE attr = 'reviewed' AND newValue = 'true'
  GROUP BY entity_id
   UNION ALL
  SELECT entity_id, NULL AS start_date, MAX(end_date) end_date FROM audit_log
  WHERE attr = 'overriden' AND newValue = 'true'
  GROUP BY entity_id
) a GROUP BY entity_id; 

можно было бы написать сильно оптимальнее вот так:

SELECT entity_id, 
  MAX(if(attr = 'reviewed', NULL, start_date)) AS start_date,
    MAX(if(attr = 'reviewed', end_date, NULL)) end_date 
FROM audit_log
WHERE (attr = 'reviewed' OR attr='overriden') AND newValue = 'true'
GROUP BY entity_id;

При сильно похожих WHERE в таких конструкциях, удобнее схлопнуть UNION и "на лету" подставлять в SELECT нужное значение через if.

hottg.com/developers_mind/57

1.8K viewsDec 2, 2021 at 15:16