trainer

WITH rnd_book AS (
         SELECT id, par_median FROM book
         WHERE genre = 'classic' AND lang = 'ru'
         ORDER BY RANDOM() LIMIT 1
     ),
     grouped AS (
         SELECT ROW_NUMBER() OVER (ORDER BY book, num) / (1600 / b.par_median) group_id,
                p.id, p.book, p.num, p.length
         FROM paragraph p
         RIGHT JOIN rnd_book b ON b.id = p.book
     ),
     comb_list AS (
         SELECT id FROM comb
         WHERE comb LIKE ANY (array['кло', 'спр', 'кил', 'фу_'])
     ),
     comb_max AS (
         SELECT pi.comb, MAX(count) cnt
         FROM par_inx pi
         RIGHT JOIN comb_list ON comb_list.id = pi.comb
         RIGHT JOIN grouped g ON g.id = pi.paragraph
         GROUP BY pi.comb
     ),
     found AS (
         SELECT pi.paragraph,
                SUM(pi.COUNT / cm.cnt::FLOAT) score
         FROM par_inx pi
         RIGHT JOIN comb_max cm ON pi.comb = cm.comb
         RIGHT JOIN grouped g ON g.id = pi.paragraph
         GROUP BY paragraph
     ),
     found_groups AS (
         SELECT group_id, id, book, num, length, f.score,
         SUM(score) OVER (PARTITION BY group_id) score_total,
         SUM(LENGTH) OVER (PARTITION BY group_id
                           ORDER BY score
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) len_acc
         FROM grouped g
         LEFT JOIN found f ON g.id = f.paragraph
     ),
     filtered AS (
         SELECT group_id
         FROM found_groups
         WHERE score_total IS NOT NULL AND len_acc > 550 AND len_acc < 650
     )
 SELECT * FROM found_groups
 WHERE group_id IN (SELECT group_id FROM filtered);




 WITH comb_id AS (
     SELECT id FROM comb
     WHERE comb IN ('авш', 'при', 'ско')
     ),
     par_id AS (
         SELECT id FROM paragraph
         WHERE book IN (
             SELECT id FROM book WHERE genre = 'prose' AND lang = 'ru'
     )),
     comb_max AS (
         SELECT comb, MAX(count) cnt
         FROM par_inx
         WHERE comb IN (SELECT id FROM comb_id) AND
             paragraph IN (SELECT id FROM par_id)
         GROUP BY comb
     ),
     grouped AS (
         SELECT ROW_NUMBER() OVER (ORDER BY book, num) / 4 group_id,
             id, book, num, length
         FROM paragraph
         WHERE id IN (SELECT id FROM par_id)
     ),
     found AS (
         SELECT pi.paragraph,
                SUM(pi.COUNT / cm.cnt::FLOAT) score
         FROM par_inx pi
         LEFT JOIN paragraph p ON pi.paragraph = p.id
         LEFT JOIN comb_max cm ON pi.comb = cm.comb
         WHERE paragraph IN (SELECT id FROM par_id) AND pi.comb IN (SELECT id FROM comb_id)
         GROUP BY paragraph
     ),
     found_groups AS (
         SELECT group_id, id, book, num, length, f.score,
         SUM(score) OVER (PARTITION BY group_id) score_total,
         SUM(length) OVER (PARTITION BY group_id
                           ORDER BY score
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) len_acc
         FROM grouped g
         LEFT JOIN found f ON g.id = f.paragraph
     )
   SELECT * FROM found_groups
   WHERE group_id IN (SELECT group_id
                      FROM found_groups
                      WHERE score_total IS NOT NULL AND len_acc > 550 AND len_acc < 650);