Warning: mkdir(): No space left on device in /var/www/hottg/post.php on line 59

Warning: file_put_contents(aCache/aDaily/2025-07-22/post/developers_mind/--): Failed to open stream: No such file or directory in /var/www/hottg/post.php on line 72
#повседневное @Developer's mind
TG Telegram Group & Channel
Developer's mind | United States America (US)
Create: Update:

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

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

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.

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

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

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.


>>Click here to continue<<

Developer's mind




Share with your best friend
VIEW MORE

United States America Popular Telegram Group (US)


Warning: Undefined array key 3 in /var/www/hottg/function.php on line 115

Fatal error: Uncaught mysqli_sql_exception: Too many connections in /var/www/db.php:16 Stack trace: #0 /var/www/db.php(16): mysqli_connect() #1 /var/www/hottg/function.php(212): db() #2 /var/www/hottg/function.php(115): select() #3 /var/www/hottg/post.php(351): daCache() #4 /var/www/hottg/route.php(63): include_once('...') #5 {main} thrown in /var/www/db.php on line 16