Mengembalikan nilai baris sebelumnya berdasarkan CTE kesatuan yang diisih mengikut tarikh adalah perlahan.

Question

从以下的体育比赛表格中：

id_	p1_id	p2_id	match_date	p1_stat	p2_stat
852666	1	2	01/01/1997	1301	249
852842	1	2	13/01/1997	2837	2441
853471	2	1	05/05/1997	1474	952
4760	2	1	25/05/1998	1190	1486
6713	2	1	18/01/1999	2084	885
9365	2	1	01/11/1999	2894	2040
11456	1	2	15/05/2000	2358	1491
13022	1	2	14/08/2000	2722	2401
29159	1	2	26/08/2002	431	2769
44915	1	2	07/10/2002	1904	482

对于选定的比赛id_，我希望返回两位选手在他们各自上一场比赛中的统计数据，无论该选手在上一场比赛中是p1还是p2。请参考下面的预期输出，其中id_ = 11456：

id_	p1_id	p2_id	match_date	p1_stat	p2_stat	p1_prev_stat	p2_prev_stat
11456	1	2	15/05/2000	2358	1491	2040	2894

在这个大小的表上，以下的SQL语句完全正常运行：

WITH cte_1 AS (
  (
    SELECT 
      id_, 
      match_date, 
      p1_id AS player_id, 
      p1_stat AS stat 
    FROM 
      test.match_table 
    UNION ALL 
    SELECT 
      id_, 
      match_date, 
      p2_id AS player_id, 
      p2_stat AS stat 
    FROM 
      test.match_table
  )
), 
cte_2 AS (
  SELECT 
    id_, 
    player_id, 
    LAG(stat) OVER (
      PARTITION BY player_id 
      ORDER BY 
        match_date, 
        id_
    ) AS prev_stat 
  FROM 
    cte_1
) 
SELECT 
  m.*, 
  cte_p1.prev_stat AS p1_prev_stat, 
  cte_p2.prev_stat AS p2_prev_stat 
FROM 
  test.match_table AS m 
  JOIN cte_2 AS cte_p1 ON cte_p1.id_ = m.id_ 
  AND cte_p1.player_id = m.p1_id 
  JOIN cte_2 AS cte_p2 ON cte_p2.id_ = m.id_ 
  AND cte_p2.player_id = m.p2_id
WHERE m.id_ = 11456
ORDER BY m.match_date

然而，实际的表格有130万行，大约需要12秒的时间。根据这个回答，问题似乎是CTE加载了所有或大部分的表格行，而不仅仅是需要的行。然而，解决方案并没有涵盖这种情况。

是否有人能提供一些建议，以改善性能？、

以下是创建小表格的SQL语句：

CREATE TABLE `match_table` (
  `id_` int NOT NULL AUTO_INCREMENT, 
  `p1_id` int NOT NULL, 
  `p2_id` int NOT NULL, 
  `match_date` date NOT NULL, 
  `p1_stat` int DEFAULT NULL, 
  `p2_stat` int DEFAULT NULL, 
  PRIMARY KEY (`id_`), 
  KEY `ix__p1_id` (`p1_id`), 
  KEY `ix__p2_id` (`p2_id`), 
  KEY `ix__match_date` (`match_date`), 
  KEY `ix__comp` (`p1_id`, `p2_id`, `match_date`)
);
INSERT INTO `match_table` 
VALUES 
  (
    4760, 2, 1, '1998-05-25', 1190, 1486
  ), 
  (6713, 2, 1, '1999-01-18', 2084, 885), 
  (
    9365, 2, 1, '1999-11-01', 2894, 2040
  ), 
  (
    11456, 1, 2, '2000-05-15', 2358, 1491
  ), 
  (
    13022, 1, 2, '2000-08-14', 2722, 2401
  ), 
  (
    29159, 1, 2, '2002-08-26', 431, 2769
  ), 
  (
    44915, 1, 2, '2002-10-07', 1904, 482
  ), 
  (
    852666, 1, 2, '1997-01-01', 1301, 249
  ), 
  (
    852842, 1, 2, '1997-01-13', 2837, 2441
  ), 
  (
    853471, 2, 1, '1997-05-05', 1474, 952
  );

Answer 1

Pertama, pastikan anda mempunyai indeks yang betul pada pangkalan data anda adalah kunci. Anda harus memastikan bahawa medan id_, p1_id, p2_id dan match_date diindeks kerana ia digunakan dalam pertanyaan anda.

Kedua, daripada menggunakan dua CTE (ungkapan biasa), gunakan fungsi LAG() terus dalam pertanyaan utama. Ini mengelakkan keperluan untuk baris pendua, sekali gus mempercepatkan pertanyaan. Berikut adalah kaedah yang boleh anda gunakan:

SELECT 
    m.*, 
    LAG(m.p1_stat) OVER (PARTITION BY m.p1_id ORDER BY m.match_date, m.id_) AS p1_prev_stat, 
    LAG(m.p2_stat) OVER (PARTITION BY m.p2_id ORDER BY m.match_date, m.id_) AS p2_prev_stat 
FROM 
    test.match_table AS m 
WHERE 
    m.id_ = 11456
ORDER BY 
    m.match_date;

Pertanyaan ini melakukan perkara berikut:

• Mengalih keluar operasi kesatuan untuk mengelakkan dua kali ganda bilangan baris.
• Melangkau langkah menyertai jadual asal dua kali.
• Gunakan fungsi LAG() terus dalam pertanyaan utama untuk mendapatkan statistik sebelumnya.

Walau bagaimanapun, jika pendekatan ini tidak memenuhi keperluan prestasi anda, pertimbangkan untuk membuat jadual ringkasan. Jadual menjejaki statistik setiap pemain untuk setiap permainan. Setiap kali keputusan perlawanan baharu ditambah, jadual ringkasan dikemas kini. Ini menggunakan lebih banyak ruang storan dan menjejaskan prestasi penulisan, tetapi meningkatkan prestasi membaca dengan ketara.