我正在嘗試生成一個運行總計，但需要不同表中每一行的最后一個值。在下面的示例中，我可以輕松地為 中的每個time值生成運行總計T，但我想要T每個時間值的運行總計P（而不是T獲取價格值的每個交易，這是微不足道的）：
給定一個交易表，T例如：
| 用戶 | 小時 | 物品 | 三角洲 |
|---|---|---|---|
| 愛麗絲 | 1 | 一種 | 1 |
| 愛麗絲 | 1 | 一種 | 2 |
| 鮑勃 | 2 | 一種 | 2 |
| 愛麗絲 | 3 | 一種 | 1 |
| 鮑勃 | 3 | 乙 | 1 |
| 愛麗絲 | 5 | 一種 | -1 |
| 鮑勃 | 5 | 乙 | 3 |
還有一個定價表，P如：
| 小時 | 物品 | 價錢 |
|---|---|---|
| 1 | 一種 | 1.1 |
| 1 | 乙 | 1.2 |
| 2 | 一種 | 2.1 |
| 2 | 乙 | 2.2 |
| 3 | 一種 | 3.1 |
| 3 | 乙 | 3.2 |
| 4 | 一種 | 4.1 |
| 4 | 乙 | 4.2 |
| 5 | 一種 | 5.1 |
| 5 | 乙 | 5.2 |
我想要一個記錄hour，P其中 auser的運行總數不為零。就像是：
| 小時 | 物品 | 價錢 | 用戶 | 運行總 |
|---|---|---|---|---|
| 1 | 一種 | 1.1 | 愛麗絲 | 3 |
| 2 | 一種 | 2.1 | 愛麗絲 | 3 |
| 2 | 一種 | 2.1 | 鮑勃 | 2 |
| 3 | 一種 | 3.1 | 愛麗絲 | 4 |
| 3 | 一種 | 3.1 | 鮑勃 | 2 |
| 3 | 乙 | 3.2 | 鮑勃 | 1 |
| 4 | 一種 | 4.1 | 愛麗絲 | 4 |
| 4 | 一種 | 4.1 | 鮑勃 | 2 |
| 4 | 乙 | 4.2 | 鮑勃 | 1 |
| 5 | 一種 | 5.1 | 愛麗絲 | 3 |
| 5 | 一種 | 5.1 | 鮑勃 | 2 |
| 5 | 乙 | 5.2 | 鮑勃 | 4 |
I'm fine with zeros or nulls instead of rows I've elided (i.e. prior to Bob having any items). The crucial thing I'm having trouble with, is that for each hour where an item has a price, I would like every user's balance.
I'm currently doing this very stupidly, in a procedural language, iterating over all hour values in P - but considering that I think I'm just looking for a filtered cartesian product between a table and a running total table, I think there must be a much better way of doing it.
My current solution of iterating over the pricing table (~3K rows in pricing table, 10K rows in transactions table) takes about 250 ms to do imperatively. The following SQL seems to do the job, but takes ~25 seconds, so I am hoping there is a better way of doing things:
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;
uj5u.com熱心網友回復：
您的代碼有 2 個建議/簡化。
首先，ORDER BY沒有LIMIT在子查詢內部的子句完全沒有用，不會影響最終結果，只是會降低查詢的性能。
因此，從 B 和 F 子查詢中洗掉它們。
此外，您正在執行 a CROSS JOIN，盡管您使用了ON子句。
這相當于 an INNER JOIN，這是您應該使用的，因為（來自Simple Select Processing）：
“CROSS JOIN”聯接運算子產生與“INNER JOIN”、“JOIN”和“,”運算子相同的結果，但查詢優化器的處理方式有所不同，因為它阻止查詢優化器對聯接中的表重新排序。應用程式程式員可以使用 CROSS JOIN 運算子直接影響為實作 SELECT 陳述句而選擇的演算法。避免使用 CROSS JOIN，除非在需要手動控制查詢優化器的特定情況下。避免在應用程式開發的早期使用 CROSS JOIN，因為這樣做是過早的優化。CROSS JOIN 的特殊處理是 SQLite 特定的功能，不是標準 SQL 的一部分。
嘗試這個：
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;
或者，另一個使用 SQLite 裸列的版本：
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);
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/360094.html