SQL HowTo: សរសេរ while-loop ដោយផ្ទាល់នៅក្នុងសំណួរ ឬ "Elementary three-way"

ម្តងម្កាល ភារកិច្ចស្វែងរកទិន្នន័យដែលពាក់ព័ន្ធដោយសំណុំសោកើតឡើង រហូតដល់យើងទទួលបានចំនួនកំណត់ត្រាសរុបដែលត្រូវការ.

ឧទាហរណ៍ "ជីវិត" បំផុតគឺបង្ហាញ 20 បញ្ហាចំណាស់ជាងគេ, រាយ នៅក្នុងបញ្ជីបុគ្គលិក (ឧទាហរណ៍នៅក្នុងនាយកដ្ឋានតែមួយ) ។ សម្រាប់ "ផ្ទាំងគ្រប់គ្រង" ជាច្រើនដែលមានការសង្ខេបខ្លីៗនៃផ្នែកនៃការងារ ប្រធានបទស្រដៀងគ្នាគឺត្រូវបានទាមទារជាញឹកញាប់។

នៅក្នុងអត្ថបទ យើងនឹងពិចារណាលើការអនុវត្តលើ PostgreSQL នៃកំណែ "ឆោតល្ងង់" នៃការដោះស្រាយបញ្ហាបែបនេះ ក្បួនដោះស្រាយ "ឆ្លាតជាង" និងស្មុគស្មាញបំផុត។ "រង្វិលជុំ" នៅក្នុង SQL ជាមួយនឹងលក្ខខណ្ឌចាកចេញពីទិន្នន័យដែលបានរកឃើញដែលអាចមានប្រយោជន៍ទាំងការអភិវឌ្ឍន៍ទូទៅ និងសម្រាប់ប្រើប្រាស់ក្នុងករណីស្រដៀងគ្នាផ្សេងទៀត។

តោះយកសំណុំទិន្នន័យសាកល្បងពី អត្ថបទមុន. ដូច្នេះថាកំណត់ត្រាលទ្ធផលមិន "លោត" ពីពេលមួយទៅពេលមួយនៅពេលដែលតម្លៃដែលបានតម្រៀបត្រូវគ្នា។ ពង្រីកសន្ទស្សន៍ប្រធានបទដោយបន្ថែមសោចម្បង. ទន្ទឹមនឹងនេះ វានឹងផ្តល់ឱ្យវានូវភាពប្លែកភ្លាមៗ ហើយធានាឱ្យយើងនូវភាពប្លែកនៃលំដាប់តម្រៀប៖

CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;

ដូច​គេ​បាន​ឮ​ដូច្នេះ​ហើយ ទើប​គេ​សរសេរ

ជាដំបូង ចូរយើងគូរចេញនូវកំណែសាមញ្ញបំផុតនៃសំណើ ដោយឆ្លងកាត់លេខសម្គាល់របស់អ្នកសំដែង អារេជាធាតុបញ្ចូល:

SELECT
  *
FROM
  task
WHERE
  owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
  task_date, id
LIMIT 20;

[សូមមើលពន្យល់.tensor.ru]

សោកសៅបន្តិច - យើងបានបញ្ជាទិញតែ 20 កំណត់ត្រាប៉ុណ្ណោះ ហើយ Index Scan បានប្រគល់មកយើងវិញ។ 960 ជួរដែលបន្ទាប់មកក៏ត្រូវតម្រៀប ... ហើយសូមព្យាយាមអានតិច។

unnest + ARRAY

ការពិចារណាដំបូងដែលនឹងជួយយើង - ប្រសិនបើយើងត្រូវការ សរុប 20 តម្រៀប កំណត់ត្រា, វាគ្រប់គ្រាន់ក្នុងការអាន មិនលើសពី 20 តម្រៀបតាមលំដាប់ដូចគ្នាសម្រាប់នីមួយៗ គន្លឹះ។ ល្អ សន្ទស្សន៍សមរម្យ (owner_id, task_date, id) យើងមាន។

ចូរប្រើយន្តការដូចគ្នានៃការស្រង់ចេញនិង "ប្រែទៅជាជួរឈរ" ធាតុតារាងអាំងតេក្រាល។, ដូច​ជា​នៅ​ក្នុង អត្ថបទចុងក្រោយ. ហើយក៏អនុវត្ត convolution ទៅ array ដោយប្រើ function ARRAY():

WITH T AS (
  SELECT
    unnest(ARRAY(
      SELECT
        t
      FROM
        task t
      WHERE
        owner_id = unnest
      ORDER BY
        task_date, id
      LIMIT 20 -- ограничиваем тут...
    )) r
  FROM
    unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
  (r).*
FROM
  T
ORDER BY
  (r).task_date, (r).id
LIMIT 20; -- ... и тут - тоже

[សូមមើលពន្យល់.tensor.ru]

អូ វា​ល្អ​ជាង​មុន​ហើយ! លឿនជាង 40% និងទិន្នន័យតិចជាង 4.5 ដង ត្រូវតែអាន។

ការបង្កើតកំណត់ត្រាតារាងតាមរយៈ CTEខ្ញុំនឹងកត់ចំណាំ ករណី​ខ្លះ ការប៉ុនប៉ងដើម្បីធ្វើការភ្លាមៗជាមួយនឹងវាលកំណត់ត្រាបន្ទាប់ពីស្វែងរកវានៅក្នុងសំណួររង ដោយគ្មាន "ការរុំ" នៅក្នុង CTE អាចនាំឱ្យ "គុណ" InitPlan សមាមាត្រទៅនឹងចំនួននៃវាលដូចគ្នាទាំងនេះ៖

SELECT
  ((
    SELECT
      t
    FROM
      task t
    WHERE
      owner_id = 1
    ORDER BY
      task_date, id
    LIMIT 1
  ).*);

Result  (cost=4.77..4.78 rows=1 width=16) (actual time=0.063..0.063 rows=1 loops=1)
  Buffers: shared hit=16
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t  (cost=0.42..387.57 rows=500 width=48) (actual time=0.030..0.030 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_1  (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4
  InitPlan 3 (returns $2)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_2  (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4"
  InitPlan 4 (returns $3)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_3  (cost=0.42..387.57 rows=500 width=48) (actual time=0.009..0.009 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4

កំណត់ត្រាដូចគ្នាត្រូវបាន "ស្វែងរក" 4 ដង... រហូតដល់ PostgreSQL 11 ឥរិយាបថនេះកើតឡើងជាទៀងទាត់ ហើយដំណោះស្រាយគឺ "រុំ" នៅក្នុង CTE ដែលជាព្រំដែនដោយគ្មានលក្ខខណ្ឌសម្រាប់កម្មវិធីបង្កើនប្រសិទ្ធភាពនៅក្នុងកំណែទាំងនេះ។

ឧបករណ៍ផ្ទុកឡើងវិញ

នៅក្នុងកំណែមុនយើងអានសរុប 200 ជួរ សម្រាប់ជាប្រយោជន៍នៃការចាំបាច់ 20. រួចទៅហើយមិនមែន 960, ប៉ុន្តែសូម្បីតែតិចជាង - តើវាអាចទៅរួចទេ?

ចូរយើងព្យាយាមប្រើចំណេះដឹងដែលយើងត្រូវការ សរុប ៦០ កំណត់ត្រា។ នោះ​គឺ​យើង​នឹង​ធ្វើ​ការ​ដក​ទិន្នន័យ​ដដែលៗ​រហូត​ដល់​ចំនួន​ដែល​យើង​ត្រូវ​ការ​ត្រូវ​បាន​ដល់។

ជំហានទី 1: ចាប់ផ្តើមបញ្ជី

ជាក់ស្តែង បញ្ជី "គោលដៅ" នៃធាតុចំនួន 20 របស់យើងគួរតែចាប់ផ្តើមដោយធាតុ "ដំបូង" សម្រាប់សោម្ចាស់_id របស់យើង។ ដូច្នេះដំបូងយើងរកឃើញបែបនេះ "ដំបូងបំផុត" សម្រាប់សោនីមួយៗ ហើយដាក់វានៅក្នុងបញ្ជីដោយតម្រៀបវាតាមលំដាប់ដែលយើងចង់បាន - (task_date, id)។

ជំហានទី 2: ស្វែងរកកំណត់ត្រា "បន្ទាប់"

ឥឡូវនេះប្រសិនបើយើងយកធាតុដំបូងពីបញ្ជីរបស់យើងហើយចាប់ផ្តើម "ជំហាន" បន្ថែមទៀតចុះក្រោមសន្ទស្សន៍ ជាមួយនឹងការរក្សាទុក owner_id-key បន្ទាប់មកកំណត់ត្រាដែលបានរកឃើញទាំងអស់គឺគ្រាន់តែជាលេខបន្ទាប់នៅក្នុងការជ្រើសរើសលទ្ធផលប៉ុណ្ណោះ។ ជាការពិតណាស់មានតែ រហូតដល់យើងឆ្លងកាត់សោដែលបានអនុវត្ត ធាតុទីពីរនៅក្នុងបញ្ជី។

ប្រសិនបើវាប្រែថាយើង "ឆ្លងកាត់" ធាតុទីពីរបន្ទាប់មក ធាតុដែលបានអានចុងក្រោយគួរតែត្រូវបានបន្ថែមទៅក្នុងបញ្ជីជំនួសឱ្យលេខទីមួយ (ជាមួយ owner_id ដូចគ្នា) បន្ទាប់ពីនោះបញ្ជីត្រូវបានតម្រៀបម្តងទៀត។

នោះគឺយើងតែងតែទទួលបានថាបញ្ជីមិនមានធាតុលើសពីមួយសម្រាប់សោនីមួយៗទេ (ប្រសិនបើធាតុត្រូវបានបញ្ចប់ហើយយើងមិនបាន "ឆ្លងកាត់" នោះធាតុដំបូងនឹងបាត់ពីបញ្ជីហើយគ្មានអ្វីនឹងត្រូវបានបន្ថែមទេ។ ), ហើយ​ពួក​គេ តែងតែតម្រៀប នៅក្នុងលំដាប់ឡើងនៃកូនសោកម្មវិធី (task_date, id) ។

ជំហានទី 3៖ ត្រង និងពង្រីកកំណត់ត្រា

នៅក្នុងផ្នែកនៃជួរដេកនៃការជ្រើសរើសឡើងវិញរបស់យើង កំណត់ត្រាមួយចំនួន rv ត្រូវបានចម្លង - ដំបូងយើងរកឃើញដូចជា "ឆ្លងកាត់ព្រំដែននៃធាតុទី 2 នៃបញ្ជី" ហើយបន្ទាប់មកយើងជំនួសលេខ 1 ពីបញ្ជី។ ដូច្នេះហើយ ការកើតឡើងដំបូងគួរតែត្រូវបានត្រងចេញ។

សំណួរចុងក្រោយដ៏គួរឱ្យភ័យខ្លាច

WITH RECURSIVE T AS (
  -- #1 : заносим в список "первые" записи по каждому из ключей набора
  WITH wrap AS ( -- "материализуем" record'ы, чтобы обращение к полям не вызывало умножения InitPlan/SubPlan
    WITH T AS (
      SELECT
        (
          SELECT
            r
          FROM
            task r
          WHERE
            owner_id = unnest
          ORDER BY
            task_date, id
          LIMIT 1
        ) r
      FROM
        unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
    )
    SELECT
      array_agg(r ORDER BY (r).task_date, (r).id) list -- сортируем список в нужном порядке
    FROM
      T
  )
  SELECT
    list
  , list[1] rv
  , FALSE not_cross
  , 0 size
  FROM
    wrap
UNION ALL
  -- #2 : вычитываем записи 1-го по порядку ключа, пока не перешагнем через запись 2-го
  SELECT
    CASE
      -- если ничего не найдено для ключа 1-й записи
      WHEN X._r IS NOT DISTINCT FROM NULL THEN
        T.list[2:] -- убираем ее из списка
      -- если мы НЕ пересекли прикладной ключ 2-й записи
      WHEN X.not_cross THEN
        T.list -- просто протягиваем тот же список без модификаций
      -- если в списке уже нет 2-й записи
      WHEN T.list[2] IS NULL THEN
        -- просто возвращаем пустой список
        '{}'
      -- пересортировываем словарь, убирая 1-ю запись и добавляя последнюю из найденных
      ELSE (
        SELECT
          coalesce(T.list[2] || array_agg(r ORDER BY (r).task_date, (r).id), '{}')
        FROM
          unnest(T.list[3:] || X._r) r
      )
    END
  , X._r
  , X.not_cross
  , T.size + X.not_cross::integer
  FROM
    T
  , LATERAL(
      WITH wrap AS ( -- "материализуем" record
        SELECT
          CASE
            -- если все-таки "перешагнули" через 2-ю запись
            WHEN NOT T.not_cross
              -- то нужная запись - первая из спписка
              THEN T.list[1]
            ELSE ( -- если не пересекли, то ключ остался как в предыдущей записи - отталкиваемся от нее
              SELECT
                _r
              FROM
                task _r
              WHERE
                owner_id = (rv).owner_id AND
                (task_date, id) > ((rv).task_date, (rv).id)
              ORDER BY
                task_date, id
              LIMIT 1
            )
          END _r
      )
      SELECT
        _r
      , CASE
          -- если 2-й записи уже нет в списке, но мы хоть что-то нашли
          WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
            TRUE
          ELSE -- ничего не нашли или "перешагнули"
            coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
        END not_cross
      FROM
        wrap
    ) X
  WHERE
    T.size < 20 AND -- ограничиваем тут количество
    T.list IS DISTINCT FROM '{}' -- или пока список не кончился
)
-- #3 : "разворачиваем" записи - порядок гарантирован по построению
SELECT
  (rv).*
FROM
  T
WHERE
  not_cross; -- берем только "непересекающие" записи

[សូមមើលពន្យល់.tensor.ru]

ដូច្នេះយើង ការជួញដូរទិន្នន័យ 50% អានសម្រាប់រយៈពេលប្រតិបត្តិ 20%. នោះគឺប្រសិនបើអ្នកមានហេតុផលដើម្បីជឿថាការអានអាចមានរយៈពេលយូរ (ឧទាហរណ៍ ទិន្នន័យច្រើនតែមិននៅក្នុងឃ្លាំងសម្ងាត់ ហើយអ្នកត្រូវចូលទៅកាន់ថាសសម្រាប់វា) បន្ទាប់មកតាមរបៀបនេះអ្នកអាចពឹងផ្អែកលើការអានតិច។

ក្នុងករណីណាក៏ដោយពេលវេលាប្រតិបត្តិបានប្រែទៅជាប្រសើរជាងនៅក្នុងជម្រើសដំបូង "ឆោតល្ងង់" ។ ប៉ុន្តែតើជម្រើស 3 ណាដែលត្រូវប្រើគឺអាស្រ័យលើអ្នក។

ប្រភព: www.habr.com