PostgreSQL ਐਂਟੀਪੈਟਰਨ: ਰਜਿਸਟਰੀ ਨੂੰ ਨੈਵੀਗੇਟ ਕਰਨਾ

ਅੱਜ SQL ਵਿੱਚ ਕੋਈ ਗੁੰਝਲਦਾਰ ਕੇਸ ਅਤੇ ਸੂਝਵਾਨ ਐਲਗੋਰਿਦਮ ਨਹੀਂ ਹੋਣਗੇ। ਕੈਪਟਨ ਸਪੱਸ਼ਟ ਦੇ ਪੱਧਰ 'ਤੇ, ਸਭ ਕੁਝ ਬਹੁਤ ਸਰਲ ਹੋਵੇਗਾ - ਆਓ ਇਸ ਨੂੰ ਕਰੀਏ ਇਵੈਂਟ ਰਜਿਸਟਰੀ ਦੇਖ ਰਿਹਾ ਹੈ ਸਮੇਂ ਅਨੁਸਾਰ ਕ੍ਰਮਬੱਧ.

ਯਾਨੀ ਡੇਟਾਬੇਸ ਵਿੱਚ ਇੱਕ ਚਿੰਨ੍ਹ ਹੈ events, ਅਤੇ ਉਸ ਕੋਲ ਇੱਕ ਖੇਤ ਹੈ ts - ਬਿਲਕੁਲ ਉਸੇ ਸਮੇਂ ਜਦੋਂ ਅਸੀਂ ਇਹਨਾਂ ਰਿਕਾਰਡਾਂ ਨੂੰ ਇੱਕ ਤਰਤੀਬਵਾਰ ਢੰਗ ਨਾਲ ਪ੍ਰਦਰਸ਼ਿਤ ਕਰਨਾ ਚਾਹੁੰਦੇ ਹਾਂ:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

ਇਹ ਸਪੱਸ਼ਟ ਹੈ ਕਿ ਸਾਡੇ ਕੋਲ ਉੱਥੇ ਇੱਕ ਦਰਜਨ ਰਿਕਾਰਡ ਨਹੀਂ ਹੋਣਗੇ, ਇਸ ਲਈ ਸਾਨੂੰ ਕੁਝ ਰੂਪ ਦੀ ਲੋੜ ਪਵੇਗੀ ਪੰਨਾ ਨੈਵੀਗੇਸ਼ਨ.

#0। "ਮੈਂ ਆਪਣੀ ਮਾਂ ਦਾ ਪੋਗ੍ਰੋਮਿਸਟ ਹਾਂ"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

ਇਹ ਲਗਭਗ ਕੋਈ ਮਜ਼ਾਕ ਨਹੀਂ ਹੈ - ਇਹ ਦੁਰਲੱਭ ਹੈ, ਪਰ ਜੰਗਲੀ ਵਿੱਚ ਪਾਇਆ ਜਾਂਦਾ ਹੈ. ਕਈ ਵਾਰ, ORM ਨਾਲ ਕੰਮ ਕਰਨ ਤੋਂ ਬਾਅਦ, SQL ਨਾਲ "ਸਿੱਧਾ" ਕੰਮ 'ਤੇ ਬਦਲਣਾ ਮੁਸ਼ਕਲ ਹੋ ਸਕਦਾ ਹੈ।

ਪਰ ਆਓ ਹੋਰ ਆਮ ਅਤੇ ਘੱਟ ਸਪੱਸ਼ਟ ਸਮੱਸਿਆਵਾਂ ਵੱਲ ਵਧੀਏ।

#1। ਆਫਸੈੱਟ

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

26 ਨੰਬਰ ਕਿੱਥੋਂ ਆਇਆ? ਇਹ ਇੱਕ ਸਕ੍ਰੀਨ ਨੂੰ ਭਰਨ ਲਈ ਐਂਟਰੀਆਂ ਦੀ ਅੰਦਾਜ਼ਨ ਸੰਖਿਆ ਹੈ। ਵਧੇਰੇ ਸਪਸ਼ਟ ਤੌਰ 'ਤੇ, 25 ਪ੍ਰਦਰਸ਼ਿਤ ਰਿਕਾਰਡ, ਪਲੱਸ 1, ਇਹ ਸੰਕੇਤ ਦਿੰਦੇ ਹਨ ਕਿ ਨਮੂਨੇ ਵਿੱਚ ਘੱਟੋ ਘੱਟ ਕੁਝ ਹੋਰ ਹੈ ਅਤੇ ਇਹ ਅੱਗੇ ਵਧਣ ਦਾ ਅਰਥ ਰੱਖਦਾ ਹੈ।

ਬੇਸ਼ੱਕ, ਇਹ ਮੁੱਲ ਬੇਨਤੀ ਦੇ ਮੁੱਖ ਭਾਗ ਵਿੱਚ "ਸੀਵਿਆ" ਨਹੀਂ ਜਾ ਸਕਦਾ, ਪਰ ਇੱਕ ਪੈਰਾਮੀਟਰ ਦੁਆਰਾ ਪਾਸ ਕੀਤਾ ਜਾ ਸਕਦਾ ਹੈ। ਪਰ ਇਸ ਕੇਸ ਵਿੱਚ, PostgreSQL ਸ਼ਡਿਊਲਰ ਇਸ ਗਿਆਨ 'ਤੇ ਭਰੋਸਾ ਕਰਨ ਦੇ ਯੋਗ ਨਹੀਂ ਹੋਵੇਗਾ ਕਿ ਮੁਕਾਬਲਤਨ ਘੱਟ ਰਿਕਾਰਡ ਹੋਣੇ ਚਾਹੀਦੇ ਹਨ - ਅਤੇ ਆਸਾਨੀ ਨਾਲ ਇੱਕ ਬੇਅਸਰ ਯੋਜਨਾ ਦੀ ਚੋਣ ਕਰੇਗਾ.

ਅਤੇ ਜਦੋਂ ਐਪਲੀਕੇਸ਼ਨ ਇੰਟਰਫੇਸ ਵਿੱਚ, ਰਜਿਸਟਰੀ ਨੂੰ ਦੇਖਣਾ ਵਿਜ਼ੂਅਲ "ਪੰਨਿਆਂ" ਦੇ ਵਿਚਕਾਰ ਬਦਲਣ ਦੇ ਰੂਪ ਵਿੱਚ ਲਾਗੂ ਕੀਤਾ ਜਾਂਦਾ ਹੈ, ਕੋਈ ਵੀ ਲੰਬੇ ਸਮੇਂ ਲਈ ਕੋਈ ਵੀ ਸ਼ੱਕੀ ਚੀਜ਼ ਨਹੀਂ ਦੇਖਦਾ ਹੈ। ਬਿਲਕੁਲ ਉਸ ਪਲ ਤੱਕ ਜਦੋਂ, ਸੁਵਿਧਾ ਲਈ ਸੰਘਰਸ਼ ਵਿੱਚ, UI/UX ਇੰਟਰਫੇਸ ਨੂੰ "ਅੰਤ ਰਹਿਤ ਸਕ੍ਰੌਲ" ਵਿੱਚ ਰੀਮੇਕ ਕਰਨ ਦਾ ਫੈਸਲਾ ਕਰਦਾ ਹੈ - ਭਾਵ, ਸਾਰੀਆਂ ਰਜਿਸਟਰੀ ਐਂਟਰੀਆਂ ਇੱਕ ਸੂਚੀ ਵਿੱਚ ਖਿੱਚੀਆਂ ਜਾਂਦੀਆਂ ਹਨ ਜਿਸਨੂੰ ਉਪਭੋਗਤਾ ਉੱਪਰ ਅਤੇ ਹੇਠਾਂ ਸਕ੍ਰੌਲ ਕਰ ਸਕਦਾ ਹੈ।

ਅਤੇ ਇਸ ਲਈ, ਅਗਲੀ ਜਾਂਚ ਦੌਰਾਨ, ਤੁਸੀਂ ਫੜੇ ਗਏ ਹੋ ਰਿਕਾਰਡ ਦੀ ਨਕਲ ਰਜਿਸਟਰੀ ਵਿੱਚ. ਕਿਉਂ, ਕਿਉਂਕਿ ਸਾਰਣੀ ਵਿੱਚ ਇੱਕ ਆਮ ਸੂਚਕਾਂਕ ਹੈ (ts), ਤੁਹਾਡੀ ਪੁੱਛਗਿੱਛ ਕਿਸ 'ਤੇ ਨਿਰਭਰ ਕਰਦੀ ਹੈ?

ਬਿਲਕੁਲ ਕਿਉਂਕਿ ਤੁਸੀਂ ਇਸ ਨੂੰ ਧਿਆਨ ਵਿੱਚ ਨਹੀਂ ਰੱਖਿਆ ts ਇੱਕ ਵਿਲੱਖਣ ਕੁੰਜੀ ਨਹੀਂ ਹੈ ਇਸ ਸਾਰਣੀ ਵਿੱਚ. ਅਸਲ ਵਿੱਚ, ਅਤੇ ਇਸਦੇ ਮੁੱਲ ਵਿਲੱਖਣ ਨਹੀਂ ਹਨ, ਅਸਲ ਸਥਿਤੀਆਂ ਵਿੱਚ ਕਿਸੇ ਵੀ "ਸਮੇਂ" ਦੀ ਤਰ੍ਹਾਂ - ਇਸਲਈ, ਇੱਕੋ ਕੁੰਜੀ ਮੁੱਲ ਨੂੰ ਛਾਂਟਣ ਦੇ ਫਰੇਮਵਰਕ ਦੇ ਅੰਦਰ ਇੱਕ ਵੱਖਰੇ ਅੰਤਮ ਕ੍ਰਮ ਦੇ ਕਾਰਨ ਦੋ ਨਾਲ ਲੱਗਦੀਆਂ ਪੁੱਛਗਿੱਛਾਂ ਵਿੱਚ ਇੱਕੋ ਰਿਕਾਰਡ ਆਸਾਨੀ ਨਾਲ ਇੱਕ ਪੰਨੇ ਤੋਂ ਦੂਜੇ ਪੰਨੇ 'ਤੇ "ਜੰਪ" ਕਰਦਾ ਹੈ।

ਦਰਅਸਲ, ਇੱਥੇ ਇੱਕ ਦੂਜੀ ਸਮੱਸਿਆ ਵੀ ਛੁਪੀ ਹੋਈ ਹੈ, ਜਿਸ ਨੂੰ ਧਿਆਨ ਵਿੱਚ ਰੱਖਣਾ ਬਹੁਤ ਮੁਸ਼ਕਲ ਹੈ - ਕੁਝ ਐਂਟਰੀਆਂ ਨਹੀਂ ਦਿਖਾਈਆਂ ਜਾਣਗੀਆਂ ਤੇ ਸਾਰੇ! ਆਖ਼ਰਕਾਰ, "ਡੁਪਲੀਕੇਟ" ਰਿਕਾਰਡਾਂ ਨੇ ਕਿਸੇ ਹੋਰ ਦੀ ਜਗ੍ਹਾ ਲੈ ਲਈ. ਸੁੰਦਰ ਤਸਵੀਰਾਂ ਦੇ ਨਾਲ ਇੱਕ ਵਿਸਤ੍ਰਿਤ ਵਿਆਖਿਆ ਲੱਭੀ ਜਾ ਸਕਦੀ ਹੈ ਇੱਥੇ ਪੜ੍ਹੋ.

ਸੂਚਕਾਂਕ ਦਾ ਵਿਸਤਾਰ ਕਰਨਾ

ਇੱਕ ਚਲਾਕ ਡਿਵੈਲਪਰ ਸਮਝਦਾ ਹੈ ਕਿ ਸੂਚਕਾਂਕ ਕੁੰਜੀ ਨੂੰ ਵਿਲੱਖਣ ਬਣਾਉਣ ਦੀ ਲੋੜ ਹੈ, ਅਤੇ ਸਭ ਤੋਂ ਆਸਾਨ ਤਰੀਕਾ ਹੈ ਇਸਨੂੰ ਇੱਕ ਸਪੱਸ਼ਟ ਤੌਰ 'ਤੇ ਵਿਲੱਖਣ ਖੇਤਰ ਨਾਲ ਵਿਸਤਾਰ ਕਰਨਾ, ਜਿਸ ਲਈ PK ਸੰਪੂਰਨ ਹੈ:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

ਅਤੇ ਬੇਨਤੀ ਬਦਲਦੀ ਹੈ:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. "ਕਰਸਰ" ਤੇ ਸਵਿਚ ਕਰੋ

ਕੁਝ ਸਮੇਂ ਬਾਅਦ, ਇੱਕ DBA ਤੁਹਾਡੇ ਕੋਲ ਆਉਂਦਾ ਹੈ ਅਤੇ ਤੁਹਾਡੀਆਂ ਬੇਨਤੀਆਂ ਤੋਂ "ਖੁਸ਼" ਹੁੰਦਾ ਹੈ ਉਹ ਆਪਣੇ ਆਫਸੈੱਟ ਨਿਯਮਾਂ ਨਾਲ ਸਰਵਰ ਨੂੰ ਨਰਕ ਵਾਂਗ ਲੋਡ ਕਰਦੇ ਹਨ, ਅਤੇ ਆਮ ਤੌਰ 'ਤੇ, ਇਸ 'ਤੇ ਸਵਿਚ ਕਰਨ ਦਾ ਸਮਾਂ ਆ ਗਿਆ ਹੈ ਦਿਖਾਏ ਗਏ ਆਖਰੀ ਮੁੱਲ ਤੋਂ ਨੈਵੀਗੇਸ਼ਨ. ਤੁਹਾਡੀ ਪੁੱਛਗਿੱਛ ਦੁਬਾਰਾ ਬਦਲਦੀ ਹੈ:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

ਤੁਸੀਂ ਸੁੱਖ ਦਾ ਸਾਹ ਲਿਆ ਜਦੋਂ ਤੱਕ ਇਹ ਨਹੀਂ ਆਇਆ ...

#3. ਸਫ਼ਾਈ ਸੂਚਕਾਂਕ

ਕਿਉਂਕਿ ਇੱਕ ਦਿਨ ਤੁਹਾਡਾ ਡੀ.ਬੀ.ਏ ਬੇਅਸਰ ਸੂਚਕਾਂਕ ਲੱਭਣ ਬਾਰੇ ਲੇਖ ਅਤੇ ਇਹ ਮਹਿਸੂਸ ਕੀਤਾ "ਨਵੀਨਤਮ ਨਹੀਂ" ਟਾਈਮਸਟੈਂਪ ਵਧੀਆ ਨਹੀਂ ਹੈ. ਅਤੇ ਮੈਂ ਤੁਹਾਡੇ ਕੋਲ ਦੁਬਾਰਾ ਆਇਆ ਹਾਂ - ਹੁਣ ਇਹ ਸੋਚ ਕੇ ਕਿ ਉਹ ਸੂਚਕਾਂਕ ਅਜੇ ਵੀ ਵਾਪਸ ਆਉਣਾ ਚਾਹੀਦਾ ਹੈ (ts DESC).

ਪਰ ਪੰਨਿਆਂ ਦੇ ਵਿਚਕਾਰ "ਜੰਪਿੰਗ" ਰਿਕਾਰਡਾਂ ਦੀ ਸ਼ੁਰੂਆਤੀ ਸਮੱਸਿਆ ਦਾ ਕੀ ਕਰਨਾ ਹੈ?.. ਅਤੇ ਸਭ ਕੁਝ ਸਧਾਰਨ ਹੈ - ਤੁਹਾਨੂੰ ਰਿਕਾਰਡਾਂ ਦੀ ਇੱਕ ਨਿਸ਼ਚਿਤ ਗਿਣਤੀ ਦੇ ਨਾਲ ਬਲਾਕਾਂ ਦੀ ਚੋਣ ਕਰਨ ਦੀ ਲੋੜ ਹੈ!

ਆਮ ਤੌਰ 'ਤੇ, ਕੌਣ ਸਾਨੂੰ "ਬਿਲਕੁਲ 26" ਨਹੀਂ, ਪਰ "26 ਤੋਂ ਘੱਟ ਨਹੀਂ" ਪੜ੍ਹਨ ਤੋਂ ਮਨ੍ਹਾ ਕਰਦਾ ਹੈ? ਉਦਾਹਰਨ ਲਈ, ਇਸ ਲਈ ਅਗਲੇ ਬਲਾਕ ਵਿੱਚ ਹਨ ਸਪੱਸ਼ਟ ਤੌਰ 'ਤੇ ਵੱਖਰੇ ਅਰਥਾਂ ਵਾਲੇ ਰਿਕਾਰਡ ts - ਫਿਰ ਬਲਾਕਾਂ ਦੇ ਵਿਚਕਾਰ "ਜੰਪਿੰਗ" ਰਿਕਾਰਡਾਂ ਵਿੱਚ ਕੋਈ ਸਮੱਸਿਆ ਨਹੀਂ ਹੋਵੇਗੀ!

ਇਸਨੂੰ ਕਿਵੇਂ ਪ੍ਰਾਪਤ ਕਰਨਾ ਹੈ ਇਹ ਇੱਥੇ ਹੈ:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

ਇੱਥੇ ਕੀ ਹੋ ਰਿਹਾ ਹੈ?

1. ਅਸੀਂ 25 ਰਿਕਾਰਡ "ਹੇਠਾਂ" ਕਰਦੇ ਹਾਂ ਅਤੇ "ਸੀਮਾ" ਮੁੱਲ ਪ੍ਰਾਪਤ ਕਰਦੇ ਹਾਂ ts.
2. ਜੇਕਰ ਉੱਥੇ ਪਹਿਲਾਂ ਹੀ ਕੁਝ ਨਹੀਂ ਹੈ, ਤਾਂ NULL ਮੁੱਲ ਨੂੰ ਨਾਲ ਬਦਲੋ -infinity.
3. ਅਸੀਂ ਪ੍ਰਾਪਤ ਮੁੱਲ ਦੇ ਵਿਚਕਾਰ ਮੁੱਲਾਂ ਦੇ ਪੂਰੇ ਹਿੱਸੇ ਨੂੰ ਘਟਾਉਂਦੇ ਹਾਂ ts ਅਤੇ ਇੰਟਰਫੇਸ ਤੋਂ ਪਾਸ ਕੀਤਾ ਗਿਆ $1 ਪੈਰਾਮੀਟਰ (ਪਿਛਲਾ "ਆਖਰੀ" ਰੈਂਡਰ ਕੀਤਾ ਮੁੱਲ)।
4. ਜੇਕਰ ਇੱਕ ਬਲਾਕ 26 ਤੋਂ ਘੱਟ ਰਿਕਾਰਡਾਂ ਨਾਲ ਵਾਪਸ ਕੀਤਾ ਜਾਂਦਾ ਹੈ, ਤਾਂ ਇਹ ਆਖਰੀ ਹੈ।

ਜਾਂ ਉਹੀ ਤਸਵੀਰ:

ਕਿਉਂਕਿ ਹੁਣ ਸਾਡੇ ਕੋਲ ਹੈ ਨਮੂਨੇ ਦੀ ਕੋਈ ਖਾਸ "ਸ਼ੁਰੂਆਤ" ਨਹੀਂ ਹੈ, ਫਿਰ ਕੋਈ ਵੀ ਚੀਜ਼ ਸਾਨੂੰ ਇਸ ਬੇਨਤੀ ਨੂੰ ਉਲਟ ਦਿਸ਼ਾ ਵਿੱਚ "ਵਿਸਤਾਰ" ਕਰਨ ਅਤੇ "ਸੰਦਰਭ ਬਿੰਦੂ" ਤੋਂ ਡੇਟਾ ਬਲਾਕਾਂ ਦੀ ਗਤੀਸ਼ੀਲ ਲੋਡਿੰਗ ਨੂੰ ਦੋਵੇਂ ਦਿਸ਼ਾਵਾਂ ਵਿੱਚ ਲਾਗੂ ਕਰਨ ਤੋਂ ਰੋਕਦੀ ਹੈ - ਦੋਵੇਂ ਹੇਠਾਂ ਅਤੇ ਉੱਪਰ।

ਨੋਟ

1. ਹਾਂ, ਇਸ ਕੇਸ ਵਿੱਚ ਅਸੀਂ ਦੋ ਵਾਰ ਸੂਚਕਾਂਕ ਨੂੰ ਐਕਸੈਸ ਕਰਦੇ ਹਾਂ, ਪਰ ਸਭ ਕੁਝ "ਪੂਰੀ ਤਰ੍ਹਾਂ ਸੂਚਕਾਂਕ ਦੁਆਰਾ" ਹੈ। ਇਸ ਲਈ, ਇੱਕ ਸਬਕਵੇਰੀ ਦਾ ਨਤੀਜਾ ਹੀ ਨਿਕਲੇਗਾ ਸਿਰਫ਼ ਇੱਕ ਵਾਧੂ ਸੂਚਕਾਂਕ ਸਕੈਨ ਲਈ.
2. ਇਹ ਬਿਲਕੁਲ ਸਪੱਸ਼ਟ ਹੈ ਕਿ ਇਹ ਤਕਨੀਕ ਸਿਰਫ ਉਦੋਂ ਵਰਤੀ ਜਾ ਸਕਦੀ ਹੈ ਜਦੋਂ ਤੁਹਾਡੇ ਕੋਲ ਮੁੱਲ ਹਨ ts ਸਿਰਫ ਮੌਕਾ ਦੇ ਕੇ ਪਾਰ ਕਰ ਸਕਦਾ ਹੈ, ਅਤੇ ਉਹਨਾਂ ਵਿੱਚੋਂ ਬਹੁਤ ਸਾਰੇ ਨਹੀਂ ਹਨ. ਜੇ ਤੁਹਾਡਾ ਆਮ ਕੇਸ "00:00:00.000 'ਤੇ ਇੱਕ ਮਿਲੀਅਨ ਰਿਕਾਰਡ" ਹੈ, ਤਾਂ ਤੁਹਾਨੂੰ ਅਜਿਹਾ ਨਹੀਂ ਕਰਨਾ ਚਾਹੀਦਾ। ਮੇਰਾ ਮਤਲਬ ਹੈ, ਤੁਹਾਨੂੰ ਅਜਿਹਾ ਮਾਮਲਾ ਨਹੀਂ ਹੋਣ ਦੇਣਾ ਚਾਹੀਦਾ। ਪਰ ਜੇ ਅਜਿਹਾ ਹੁੰਦਾ ਹੈ, ਤਾਂ ਵਿਸਤ੍ਰਿਤ ਸੂਚਕਾਂਕ ਦੇ ਨਾਲ ਵਿਕਲਪ ਦੀ ਵਰਤੋਂ ਕਰੋ।

ਸਰੋਤ: www.habr.com