postgresql-折射查询的怪物

3pmvbmvn 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(275)

不知何故，我已经达到了我迄今为止最大的查询记录，幸运的是它的结构非常好。。。但它真的很难看，我不知道是否有任何方法，我可以折射它与一点知识，从社会，它将不胜感激！
这是一个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代表一个整数（在最下面）

sql postgresql

来源：https://stackoverflow.com/questions/62436600/postgresql-refractoring-a-monster-of-a-query

2条答案

按热度按时间

fhg3lkii1#

首先，我要从格式化开始。

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;

它已经短了一点，更容易阅读了。
下一步是在 select 子句，并将它们移到cte中。

WITH image_count AS (
  SELECT
    COUNT(*)::integer c AS total_choices,
    ROW_NUMBER() OVER (ORDER BY b.choice ASC) AS real_choice
  FROM files.main p
  INNER JOIN files.main b ON b.platform = p.platform AND b.reference = p.reference
  WHERE p.id = $1
)
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 (image_count.real_choice::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,

  total_image_count.real_choice, -- Image Count
  total_image_count.total_choices -- Image Total Count

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
CROSS JOIN total_image_count
WHERE p.id = $1;

就我个人而言，我可能会把它留在那里。对我来说这并不难，但这当然是主观的。
如果我更进一步，我可能会查看是谁在调用查询，看看调用者是否能够接受一些字符串操作逻辑。剩下的大部分复杂性只是case语句和字符串concats，如果是这样的话，调用它的应用程序可能会处理它们。

赞(0）回复(0）举报 2021-07-26

jobtbby32#

尝试在事务块中使用临时表来创建中间结果，而不是庞大、复杂的sql语句。它们更容易阅读和维护。大致思路如下：

Start transaction block
Create Temp table with just needed columns
Sql statement to perform joins into table (repeat as needed)
Sql statement to perform conversions
Sql statement to move results to permanent location
Commit (end of transaction)

commit块确保其行为与单个语句相同（确保autocommit已关闭）。使用此方法可以将问题分解为易于阅读和修改的逻辑部分。你会在效率上受到小小的打击，但它会让你解决最复杂的问题

赞(0）回复(0）举报 2021-07-26

我来回答

postgresql-折射查询的怪物

2条答案

相关问题

热门标签

最新问答