SQLite Běží Celkem (ale klíčem na hodnoty v jiné tabulce)

Question 1

Snažím se vytvořit průběžný součet, ale třeba poslední hodnotu pro každý řádek v jiné tabulce. V následujícím příkladu, mohu snadno vyrábět průběžný součet pro každou time hodnota v T, ale rád bych se běží celkem T pro každou hodnotu času v P (spíše než pro každou transakci v T dostat cenu, což je triviální):

Dané tabulky transakcí T jako:

uživatel	hodinu	položka	delta
Alice	1	A	1
Alice	1	A	2
Bob	2	A	2
Alice	3	A	1
Bob	3	B	1
Alice	5	A	-1
Bob	5	B	3

A cenová tabulka P jako:

hodinu	položka	cena
1	A	1.1
1	B	1.2
2	A	2.1
2	B	2.2
3	A	3.1
3	B	3.2
4	A	4.1
4	B	4.2
5	A	5.1
5	B	5.2

Rád bych záznam pro každou hour v P kde userběží celkem je non-nulové. Něco jako:

hodinu	položka	cena	uživatel	running_total
1	A	1.1	Alice	3
2	A	2.1	Alice	3
2	A	2.1	Bob	2
3	A	3.1	Alice	4
3	A	3.1	Bob	2
3	B	3.2	Bob	1
4	A	4.1	Alice	4
4	A	4.1	Bob	2
4	B	4.2	Bob	1
5	A	5.1	Alice	3
5	A	5.1	Bob	2
5	B	5.2	Bob	4

Jsem v pořádku s nulami nebo nulls místo řádků jsem zmenšován (tj. před Bob s žádné položky). Zásadní věc, kterou mám problémy, je, že na každou hodinu, kde položka má cenu, rád bych každý uživatel je rovnováha.

Já jsem v současné době dělá to velmi hloupě, v procedurální jazyk, iterace přes všechny hour hodnoty v P - ale vzhledem k tomu, že si myslím, že jen hledám filtrované kartézský součin mezi tabulkou a běží celkem tabulky, myslím, že tam musí být lepší způsob, jak to udělat.

Moje aktuální řešení iterace přes cenovou tabulku (~3K řádků v tabulce cen, 10K řádků transakcí v tabulce) trvá asi 250 ms dělat povinně. Následující SQL zdá se, že dělat svou práci, ale trvá ~25 sekund, takže jsem doufal, že existuje lepší způsob, jak dělat věci:

with ranked_b as (
    select F.*, row_number() over (partition by p_hour, user, item order by hour desc) as rn
    from (select P.hour as p_hour, P.price, B.*  from P cross join (select distinct a.hour, a.user, a.item, sum(a.delta) over (partition by a.user, a.item order by a.hour) running_total from T a order by a.hour) B on P.item=B.item and B.hour<=P.hour  order by P_hour, B.user, B.item, B.hour) F
)  SELECT p_hour as hour, item, price, user, running_total from ranked_b where rn=1;

Question 2

K dispozici jsou 2 návrhy/zjednodušení pro váš kód.

První, ORDER BY doložka bez LIMIT uvnitř poddotaz je naprosto k ničemu a nemá vliv na konečný výsledek, až na to, že snižuje dotazu výkon.
Takže, je odstranit oba z B a F poddotazy.

Také, děláte CROSS JOIN, i když budete používat ON doložka.
To je ekvivalentní k INNER JOIN a to je to, co byste měli použít, protože (z Jednoduché Vybrat Zpracování):

"CROSS PŘIPOJIT" připojit provozovatel produkuje stejný výsledek jako "VNITŘNÍ spojení", "PŘIPOJIT" a "," operátoři, ale je nakládáno odlišně na základě dotazu optimizer v tom, že zabraňuje query optimizer z pořadí tabulek ve spojit. Programátor aplikace může použít KŘÍŽOVÉ spojení provozovatel přímo ovlivnit algoritmus, který je vybrán, aby provedení příkazu SELECT. Vyhněte se pomocí CROSS JOIN s výjimkou konkrétní situace, kdy ruční ovládání optimalizátor dotazu je je to žádoucí. Vyhněte se pomocí CROSS JOIN brzy v rozvoji aplikace, jako je to předčasná optimalizace. Speciální manipulace CROSS JOIN je SQLite-specifické funkce a není součástí standardní SQL.

Zkuste toto:

WITH ranked_b AS (
  SELECT F.*, ROW_NUMBER() OVER (PARTITION BY p_hour, user, item ORDER BY hour DESC) rn
  FROM (
    SELECT P.hour p_hour, P.price, B.*  
    FROM P 
    INNER JOIN (
      SELECT DISTINCT hour, user, item, 
             SUM(delta) OVER (PARTITION BY user, item ORDER BY hour) running_total 
      FROM T
    ) B ON P.item = B.item AND B.hour <= P.hour  
  ) F
)  
SELECT p_hour hour, item, price, user, running_total 
FROM ranked_b 
WHERE rn = 1;

Nebo další verze, která využívá SQLite je holé sloupy:

SELECT p_hour hour, item, price, user, running_total
FROM (
  SELECT P.hour p_hour, P.price, B.*  
  FROM P 
  INNER JOIN (
    SELECT DISTINCT hour, user, item, 
           SUM(delta) OVER (PARTITION BY user, item ORDER BY hour) running_total 
    FROM T
  ) B ON P.item = B.item AND B.hour <= P.hour  
) F
GROUP BY p_hour, user, item
HAVING MAX(hour);

Viz demo.

forpas · Answer 1 · 2021-11-15T15:25:27

K dispozici jsou 2 návrhy/zjednodušení pro váš kód.

První, ORDER BY doložka bez LIMIT uvnitř poddotaz je naprosto k ničemu a nemá vliv na konečný výsledek, až na to, že snižuje dotazu výkon.
Takže, je odstranit oba z B a F poddotazy.

Také, děláte CROSS JOIN, i když budete používat ON doložka.
To je ekvivalentní k INNER JOIN a to je to, co byste měli použít, protože (z Jednoduché Vybrat Zpracování):

"CROSS PŘIPOJIT" připojit provozovatel produkuje stejný výsledek jako "VNITŘNÍ spojení", "PŘIPOJIT" a "," operátoři, ale je nakládáno odlišně na základě dotazu optimizer v tom, že zabraňuje query optimizer z pořadí tabulek ve spojit. Programátor aplikace může použít KŘÍŽOVÉ spojení provozovatel přímo ovlivnit algoritmus, který je vybrán, aby provedení příkazu SELECT. Vyhněte se pomocí CROSS JOIN s výjimkou konkrétní situace, kdy ruční ovládání optimalizátor dotazu je je to žádoucí. Vyhněte se pomocí CROSS JOIN brzy v rozvoji aplikace, jako je to předčasná optimalizace. Speciální manipulace CROSS JOIN je SQLite-specifické funkce a není součástí standardní SQL.

Zkuste toto:

WITH ranked_b AS (
  SELECT F.*, ROW_NUMBER() OVER (PARTITION BY p_hour, user, item ORDER BY hour DESC) rn
  FROM (
    SELECT P.hour p_hour, P.price, B.*  
    FROM P 
    INNER JOIN (
      SELECT DISTINCT hour, user, item, 
             SUM(delta) OVER (PARTITION BY user, item ORDER BY hour) running_total 
      FROM T
    ) B ON P.item = B.item AND B.hour <= P.hour  
  ) F
)  
SELECT p_hour hour, item, price, user, running_total 
FROM ranked_b 
WHERE rn = 1;

Nebo další verze, která využívá SQLite je holé sloupy:

SELECT p_hour hour, item, price, user, running_total
FROM (
  SELECT P.hour p_hour, P.price, B.*  
  FROM P 
  INNER JOIN (
    SELECT DISTINCT hour, user, item, 
           SUM(delta) OVER (PARTITION BY user, item ORDER BY hour) running_total 
    FROM T
  ) B ON P.item = B.item AND B.hour <= P.hour  
) F
GROUP BY p_hour, user, item
HAVING MAX(hour);

Viz demo.

Děkuji moc!!! Druhá verze je asi 5x rychlejší než moje kludged verzi své údaje, a je většinou použitelný. Já jsem pořád zmatený, že stavební tabulky bezpodmínečně v Pythonu je stále více než o řád rychleji.

SQLite Běží Celkem (ale klíčem na hodnoty v jiné tabulce)

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii