Uzyskaj przyrostowe wartości zagregowanej wartości w połączonej tabeli

Mam dwie tabele w bazie danych MySQL 5.7.22: postsi reasons. Każdy wiersz postu ma i należy do wielu wierszy przyczyny. Każdy powód ma przypisaną wagę, a zatem każdy post ma z nim łączną łączną wagę.

Dla każdego przyrostu 10 punktów wagi (tj. Dla 0, 10, 20, 30 itd.) Chcę uzyskać liczbę postów, których łączna waga jest mniejsza lub równa temu przyrostowi. Spodziewałbym się, że wyniki będą wyglądać mniej więcej tak:

 weight | post_count
--------+------------
      0 | 0
     10 | 5
     20 | 12
     30 | 18
    ... | ...
    280 | 20918
    290 | 21102
    ... | ...
   1250 | 118005
   1260 | 118039
   1270 | 118040

Całkowite masy są w przybliżeniu normalnie rozłożone, z kilkoma bardzo niskimi wartościami i kilkoma bardzo wysokimi wartościami (maksimum wynosi obecnie 1277), ale większość pośrodku. Istnieje nieco mniej niż 120 000 wierszy postsi około 120 cali reasons. Każdy post ma średnio 5 lub 6 powodów.

Odpowiednie części tabel wyglądają tak:

CREATE TABLE `posts` (
  id BIGINT PRIMARY KEY
);

CREATE TABLE `reasons` (
  id BIGINT PRIMARY KEY,
  weight INT(11) NOT NULL
);

CREATE TABLE `posts_reasons` (
  post_id BIGINT NOT NULL,
  reason_id BIGINT NOT NULL,
  CONSTRAINT fk_posts_reasons_posts (post_id) REFERENCES posts(id),
  CONSTRAINT fk_posts_reasons_reasons (reason_id) REFERENCES reasons(id)
);

Do tej pory próbowałem upuścić identyfikator posta i całkowitą wagę do widoku, a następnie połączyć się z tym widokiem, aby uzyskać zagregowaną liczbę:

CREATE VIEW `post_weights` AS (
    SELECT 
        posts.id,
        SUM(reasons.weight) AS reason_weight
    FROM posts
    INNER JOIN posts_reasons ON posts.id = posts_reasons.post_id
    INNER JOIN reasons ON posts_reasons.reason_id = reasons.id
    GROUP BY posts.id
);

SELECT
    FLOOR(p1.reason_weight / 10) AS weight,
    COUNT(DISTINCT p2.id) AS cumulative
FROM post_weights AS p1
INNER JOIN post_weights AS p2 ON FLOOR(p2.reason_weight / 10) <= FLOOR(p1.reason_weight / 10)
GROUP BY FLOOR(p1.reason_weight / 10)
ORDER BY FLOOR(p1.reason_weight / 10) ASC;

Jest to jednak niezwykle wolne - pozwalam mu działać przez 15 minut bez przerywania, czego nie mogę zrobić w produkcji.

Czy istnieje bardziej skuteczny sposób to zrobić?

Jeśli chcesz przetestować cały zestaw danych, możesz go pobrać tutaj . Plik ma około 60 MB, rozwija się do około 250 MB. Alternatywnie istnieje 12000 wierszy w GitHub GIST tutaj .

Używanie funkcji lub wyrażeń w warunkach JOIN jest zwykle złym pomysłem, mówię zwykle, ponieważ niektórzy optymalizatorzy potrafią sobie z tym poradzić całkiem dobrze i mimo to wykorzystywać indeksy. Sugerowałbym utworzenie tabeli dla odważników. Coś jak:

CREATE TABLE weights
( weight int not null primary key 
);

INSERT INTO weights (weight) VALUES (0),(10),(20),...(1270);

Upewnij się, że masz indeksy na posts_reasons:

CREATE UNIQUE INDEX ... ON posts_reasons (reason_id, post_id);

Zapytanie takie jak:

SELECT w.weight
     , COUNT(1) as post_count
FROM weights w
JOIN ( SELECT pr.post_id, SUM(r.weight) as sum_weight     
       FROM reasons r
       JOIN posts_reasons pr
             ON r.id = pr.reason_id
       GROUP BY pr.post_id
     ) as x
    ON w.weight > x.sum_weight
GROUP BY w.weight;

Moja maszyna w domu ma prawdopodobnie 5-6 lat, ma procesor Intel (R) Core i5-3470 przy 3,20 GHz i 8 GB pamięci RAM.

uname -a Linux dustbite 4.16.6-302.fc28.x86_64 # 1 SMP Środa 2 maja 00:07:06 UTC 2018 x86_64 x86_64 x86_64 GNU / Linux

Testowałem przeciwko:

https://drive.google.com/open?id=1q3HZXW_qIZ01gU-Krms7qMJW3GCsOUP5

MariaDB [test3]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.2.14-MariaDB |
+-----------------+
1 row in set (0.00 sec)


SELECT w.weight
     , COUNT(1) as post_count
FROM weights w
JOIN ( SELECT pr.post_id, SUM(r.weight) as sum_weight     
       FROM reasons r
       JOIN posts_reasons pr
             ON r.id = pr.reason_id
       GROUP BY pr.post_id
     ) as x
    ON w.weight > x.sum_weight
GROUP BY w.weight;

+--------+------------+
| weight | post_count |
+--------+------------+
|      0 |          1 |
|     10 |       2591 |
|     20 |       4264 |
|     30 |       4386 |
|     40 |       5415 |
|     50 |       7499 |
[...]   
|   1270 |     119283 |
|   1320 |     119286 |
|   1330 |     119286 |
[...]
|   2590 |     119286 |
+--------+------------+
256 rows in set (9.89 sec)

Jeśli wydajność jest krytyczna i nic innego nie pomaga, możesz utworzyć tabelę podsumowań dla:

SELECT pr.post_id, SUM(r.weight) as sum_weight     
FROM reasons r
JOIN posts_reasons pr
    ON r.id = pr.reason_id
GROUP BY pr.post_id

Możesz utrzymywać tę tabelę za pomocą wyzwalaczy

Ponieważ dla każdej masy w odważnikach należy wykonać pewną pracę, może być korzystne ograniczenie tego stołu.

    ON w.weight > x.sum_weight 
WHERE w.weight <= (select MAX(sum_weights) 
                   from (SELECT SUM(weight) as sum_weights 
                   FROM reasons r        
                   JOIN posts_reasons pr
                       ON r.id = pr.reason_id 
                   GROUP BY pr.post_id) a
                  ) 
GROUP BY w.weight

Ponieważ miałem dużo niepotrzebnych wierszy w tabeli wag (maks. 2590), powyższe ograniczenie skróciło czas wykonania z 9 do 4 sekund.

W MySQL zmienne mogą być używane w zapytaniach zarówno do obliczenia na podstawie wartości w kolumnach, jak i do wyrażenia nowych, obliczonych kolumn. W takim przypadku użycie zmiennej powoduje wydajne zapytanie:

SELECT
  weight,
  @cumulative := @cumulative + post_count AS post_count
FROM
  (SELECT @cumulative := 0) AS x,
  (
    SELECT
      FLOOR(reason_weight / 10) * 10 AS weight,
      COUNT(*)                       AS post_count
    FROM
      (
        SELECT 
          p.id,
          SUM(r.weight) AS reason_weight
        FROM
          posts AS p
          INNER JOIN posts_reasons AS pr ON p.id = pr.post_id
          INNER JOIN reasons AS r ON pr.reason_id = r.id
        GROUP BY
          p.id
      ) AS d
    GROUP BY
      FLOOR(reason_weight / 10)
    ORDER BY
      FLOOR(reason_weight / 10) ASC
  ) AS derived
;

dTabela pochodzi to rzeczywiście twój post_weightswidok. Dlatego jeśli planujesz zachować widok, możesz go użyć zamiast tabeli pochodnej:

SELECT
  weight,
  @cumulative := @cumulative + post_count AS post_count
FROM
  (SELECT @cumulative := 0),
  (
    SELECT
      FLOOR(reason_weight / 10) * 10 AS weight,
      COUNT(*)                       AS post_count
    FROM
      post_weights
    GROUP BY
      FLOOR(reason_weight / 10)
    ORDER BY
      FLOOR(reason_weight / 10) ASC
  ) AS derived
;

Demo tego rozwiązania, które wykorzystuje zwięzłą edycję zredukowanej wersji twojej instalacji, można znaleźć i zagrać w SQL Fiddle .