Advertisement
xolcman

trainer

Nov 24th, 2022 (edited)
481
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.37 KB | None | 0 0
  1. WITH comb_id AS (
  2.          SELECT id FROM comb
  3.          WHERE comb IN ('авш', 'ива')
  4.          ),
  5.          par_id AS (
  6.          SELECT id FROM paragraph
  7.          WHERE book IN (
  8.              SELECT id FROM book WHERE genre = 'prose' AND lang = 'ru'
  9.          )),
  10.          comb_max AS (
  11.          SELECT comb, MAX(COUNT) cnt
  12.          FROM par_inx
  13.          WHERE comb IN (SELECT id FROM comb_id) AND paragraph IN (SELECT id FROM par_id) GROUP BY comb
  14.          ),
  15.          found AS (
  16.              SELECT pi.paragraph,
  17.                     SUM(pi.COUNT / cm.cnt::FLOAT) score,
  18.                     p.book, p.num, p.LENGTH
  19.              FROM par_inx pi
  20.              LEFT JOIN paragraph p ON pi.paragraph = p.id
  21.              LEFT JOIN comb_max cm ON pi.comb = cm.comb
  22.              WHERE p.LENGTH < 1000 AND paragraph IN (SELECT id FROM par_id) AND pi.comb IN (SELECT id FROM comb_id)
  23.              GROUP BY paragraph, p.book, p.num, p.LENGTH
  24.              ORDER BY score
  25.          )
  26.      SELECT * FROM found LIMIT 10;
  27.  
  28.  
  29.  WITH comb_id AS (
  30.      SELECT id FROM comb
  31.      WHERE comb IN ('авш', 'при', 'ско')
  32.      ),
  33.      par_id AS (
  34.          SELECT id FROM paragraph
  35.          WHERE book IN (
  36.              SELECT id FROM book WHERE genre = 'prose' AND lang = 'ru'
  37.      )),
  38.      comb_max AS (
  39.          SELECT comb, MAX(COUNT) cnt
  40.          FROM par_inx
  41.          WHERE comb IN (SELECT id FROM comb_id) AND
  42.              paragraph IN (SELECT id FROM par_id)
  43.          GROUP BY comb
  44.      ),
  45.      pre_found AS (
  46.          SELECT pi.paragraph, p.book, p.num
  47.          FROM par_inx pi
  48.          LEFT JOIN paragraph p ON pi.paragraph = p.id
  49.          WHERE p.LENGTH < 1000 AND paragraph IN (SELECT id FROM par_id) AND pi.comb IN (SELECT id FROM comb_id)
  50.          GROUP BY paragraph, p.book, p.num
  51.          LIMIT 100
  52.      ),
  53.      adj_par AS (
  54.          SELECT paragraph AS GROUP, p.id, p.book, p.num, p.LENGTH
  55.          FROM pre_found pf
  56.          LEFT JOIN paragraph p ON pf.book = p.book AND ABS(pf.num - p.num) < 3
  57.      ),
  58.      found AS (
  59.          SELECT pi.paragraph,
  60.                 SUM(pi.COUNT / cm.cnt::FLOAT) score
  61.          FROM par_inx pi
  62.          LEFT JOIN comb_max cm ON pi.comb = cm.comb
  63.          WHERE pi.paragraph IN (SELECT id FROM adj_par)
  64.          GROUP BY pi.paragraph
  65.      )
  66.  SELECT * FROM adj_par a LEFT JOIN found f ON a.id = f.paragraph ORDER BY a.GROUP, a.id;
  67.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement