不知何故,我已经达到了我迄今为止最大的查询记录,幸运的是它的结构非常好。。。但它真的很难看,我不知道是否有任何方法,我可以折射它与一点知识,从社会,它将不胜感激!
这是一个10行表中执行时间为5毫秒的怪物
SELECT
q.frontend AS platform,
r.frontend AS library,
CONCAT( -- Image Reference eg. px_iej1k321foa8_0
CASE WHEN p.is_temporary THEN q.prefix
ELSE (SELECT prefix FROM files.platform a WHERE a.platform = 'local') END,
'_', CASE WHEN p.is_temporary THEN p.reference ELSE p.file_name END,
CASE WHEN (
(SELECT real_choice FROM -- Image Count
(SELECT id, ROW_NUMBER() OVER (ORDER BY b.choice ASC)
AS real_choice FROM files.main b
WHERE b.platform = p.platform AND b.reference = p.reference
) c WHERE c.id = p.id)::integer) = 1 THEN '' ELSE CONCAT('_', p.choice)
END
) AS reference,
CONCAT( -- Image Directory
'https://',
CASE WHEN p.is_temporary THEN q.temp_bucket ELSE q.bucket END,
'/', t.path, '/', p.file_name, '.', s.name
) AS directory,
CASE WHEN u.path IS NOT NULL AND w.name IS NOT NULL THEN
CONCAT( -- Image Thumbnail
'https://',
CASE WHEN p.is_temporary THEN q.temp_bucket ELSE q.bucket END,
'/', u.path, '/', p.file_name, '.', w.name
) END AS thumbnail,
CONCAT(q.file_source_path, p.reference) AS source, -- Image Original Source
v.frontend AS rating,
(SELECT real_choice FROM -- Image Count
(SELECT id, ROW_NUMBER() OVER (ORDER BY b.choice ASC)
AS real_choice FROM files.main b
WHERE b.platform = p.platform AND b.reference = p.reference
) c WHERE c.id = p.id)::integer AS choice,
(SELECT COUNT(*) FROM files.main b -- Image Total Count
WHERE b.platform = p.platform
AND b.reference = p.reference
)::integer AS total_choices
FROM files.main p
LEFT JOIN files.platform q
ON p.platform = q.id
LEFT JOIN files.library r
ON p.library = r.id
LEFT JOIN files.extension s
ON p.extension = s.id
LEFT JOIN files.path t
ON p.path = t.id
LEFT JOIN files.path u
ON p.path_thumbnail = u.id
LEFT JOIN files.rating v
ON p.rating = v.id
LEFT JOIN files.extension w
ON p.extension_thumbnail = w.id
WHERE p.id = $1;
而$1代表一个整数(在最下面)
2条答案
按热度按时间fhg3lkii1#
首先,我要从格式化开始。
它已经短了一点,更容易阅读了。
下一步是在
select
子句,并将它们移到cte中。就我个人而言,我可能会把它留在那里。对我来说这并不难,但这当然是主观的。
如果我更进一步,我可能会查看是谁在调用查询,看看调用者是否能够接受一些字符串操作逻辑。剩下的大部分复杂性只是case语句和字符串concats,如果是这样的话,调用它的应用程序可能会处理它们。
jobtbby32#
尝试在事务块中使用临时表来创建中间结果,而不是庞大、复杂的sql语句。它们更容易阅读和维护。大致思路如下:
commit块确保其行为与单个语句相同(确保autocommit已关闭)。使用此方法可以将问题分解为易于阅读和修改的逻辑部分。你会在效率上受到小小的打击,但它会让你解决最复杂的问题