| Autor | Zpráva | ||
|---|---|---|---|
| Petr Ká Profil |
Ahoj,
potřeboval bych poradit, zda jde dotazem seskupovat následující záznamy: item_id, quantity, type ------------------------------- 1;2;PLUS 1;1;PLUS 1;2;MINUS 1;1;PLUS 1;1;PLUS tak, abych dostal následující přehled item_id, quantity, type ------------------------------- 1;3;PLUS 1;2;MINUS 1;2;PLUS tedy, abych to shrnul: Potřebuji seskupit záznamy dle "item_id" do změny "type" a spočítat SUM nad "quantity". je to výbec možné? Děkuji |
||
| Kajman Profil |
#2 · Zasláno: 12. 4. 2016, 13:51:08
Mysql na to není vhodná. Pokud by tam byl sloupec, podle kterého se to řadí, tak by to asi šlo v postresql nebo oracle.
|
||
| Petr Ká Profil |
#3 · Zasláno: 12. 4. 2016, 13:52:51
Kajman:
Je tam samozřejmě ID a vícero sloupců, nechtěl jsem to zesložiťovat. Do dotazu jsem vložil EDIT: Petr Ká: > tedy, abych to shrnul: > Potřebuji seskupit záznamy dle "item_id" do změny "type" a spočítat SUM nad "quantity". |
||
| Kajman Profil |
#4 · Zasláno: 12. 4. 2016, 14:00:14
V mysql by možná šel udělat dotaz, který s pomocí uživatelských proměnných testujících poslední type očísluje jednotlivé bloky, ten pak obalit a groupovat podle něj a item_id. Ale uživatelské proměnné jsou občas ošidné, proto bude jistější nějaká jiná databáze s podporou analytických dotazů.
|
||
| Petr Ká Profil |
#5 · Zasláno: 12. 4. 2016, 14:03:05
Kajman:
Takže jestli to dobře chápu, bude lepší při vkládání záznamu si prvně vytáhnout, zda není poslední záznam "tohoto typu" - když ano, incriminovat - když ne, přidat... Díky za konzultaci |
||
| Kajman Profil |
Nejen při vkládání, ale i při delete a update.
S uživ. proměnnými by to bylo něco jako SELECT t.item_id,
Sum(t.quantity) quantity,
t.type
FROM (SELECT x.*,
@blok := @blok + ( @posledni_type != x.type ) blok,
@posledni_type := x.type pt
FROM (SELECT 1 radek,
1 item_id,
2 quantity,
'PLUS' type
UNION ALL
SELECT 2 radek,
1 item_id,
1 quantity,
'PLUS' type
UNION ALL
SELECT 3 radek,
1 item_id,
2 quantity,
'MINUS' type
UNION ALL
SELECT 4 radek,
1 item_id,
1 quantity,
'PLUS' type
UNION ALL
SELECT 5 radek,
1 item_id,
1 quantity,
'PLUS' type) x
CROSS JOIN (SELECT @blok := 0,
@posledni_type := '') v
ORDER BY x.item_id, x.radek) t
GROUP BY t.item_id,
t.type,
t.blok
ORDER BY Min(t.radek) |
||
| TomášK Profil |
Co něco takového (bez vyzkoušení):
SELECT
item_id, group_id, type, SUM(quantity)
FROM (
SELECT
t.item_id, t.quantity, t.type, MIN(t2.id) AS group_id
FROM
t
JOIN t t2 ON t2.id > t.id AND t2.item_id = t.item_id AND t2.type != t.type
GROUP BY t.id, t.item_id, t.quantity, t.type
) extended_t
GROUP BY item_id, group_id, typePředpokládám existenci sloupce id, podle kterého řadím. Idea je dohledat záznam, u kterého se změní typ a pak groupovat podle něj. Ale je to kvadratické narozdíl od předchozích řešení. Edit: doplněno o Kajmanovy připomínky |
||
| Kajman Profil |
#8 · Zasláno: 12. 4. 2016, 15:34:57
TomášK:
V joinu by asi měla být podmínka na stejné item_id. Za závorkou to bude chtít alias, ale jinak by to mohlo fungovat (nad malými tabulkami). |
||
| CZechBoY Profil |
A ten obycejnej select
select id, sum(quantity), type from xxx group by id, type Nebo proc je potreba pocitat do zmeny typu? Moderátor juriad: Tvůj projev je špatně srozumitelný, piš prosím s diakritikou.
|
||
| Petr Ká Profil |
CZechBoY:
Ne, jde o statistiku. Byl to spíše dotaz, můžu to řešit na straně PHP, ale určitě je to podle mě lepší na straně DB. TomášK: > Co něco takového (bez vyzkoušení): SELECT item_id, group_id, type, SUM(quantity) FROM ( SELECT t.item_id, t.quantity, t.type, MIN(t2.id) AS group_id FROM t JOIN t t2 ON t2.id > t.id AND t2.item_id = t.item_id AND t2.type != t.type GROUP BY t.id, t.item_id, t.quantity, t.type ) extended_t GROUP BY item_id, group_id, type Toto bohužel nefunguje. Zkusím se s tím ještě poprat nějak přes noc, kdyžtak sem dám řešení. |
||
| Petr Ká Profil |
#11 · Zasláno: 12. 4. 2016, 21:42:37
Tak jsem to vyřešil následující procedurou:
DROP PROCEDURE IF EXISTS GROUP_TEST;
DELIMITER ;;
CREATE PROCEDURE GROUP_TEST(IN uid INT(11), IN rid INT(11))
BEGIN
DECLARE done boolean DEFAULT FALSE;
DECLARE _reason,_reason2 char(6);
DECLARE _id,_item_id, _item_id2 int(11);
DECLARE _quantity float;
DECLARE _date timestamp;
DECLARE `mcurs` CURSOR FOR SELECT `id`,`date`,`item_id`,`quantity`,`reason` FROM `warehouse_restaurant_stock_stats` WHERE `user_id`=uid AND `restaurant_id`=rid ORDER BY id ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DROP TEMPORARY TABLE IF EXISTS `grpTestTbl`;
CREATE TEMPORARY TABLE IF NOT EXISTS `grpTestTbl` (
`id` int(11) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`item_id` int(11) NOT NULL,
`quantity` float NOT NULL,
`reason` enum('USAGE','INV','NASKL','VYSKL') NOT NULL
);
OPEN mcurs;
iteration: LOOP
FETCH mcurs INTO _id, _date, _item_id, _quantity, _reason;
IF done THEN
LEAVE iteration;
END IF;
IF (_item_id = _item_id2 AND _reason = _reason2 AND _item_id2 IS NOT NULL AND _reason2 IS NOT NULL) THEN
UPDATE grpTestTbl SET quantity=quantity+_quantity WHERE item_id=_item_id ORDER BY id DESC LIMIT 1;
ELSE
INSERT INTO grpTestTbl (id, date,item_id,quantity,reason)
VALUES (_id, _date, _item_id, _quantity, _reason);
END IF;
SELECT _item_id, _reason INTO _item_id2, _reason2;
END LOOP;
CLOSE mcurs;
SELECT * FROM grpTestTbl;
END;
;;
DELIMITER ;ale na "blbých" tisíci záznamech to pracuje 2.5 sekundy... Nejde to nějak zoptimalizovat? |
||
| Kajman Profil |
#12 · Zasláno: 13. 4. 2016, 08:26:48
Zkuste upravit ty dotazy z [#6] a [#7].
U obou musíte doplnit where. U 7 dát oba filtry i do spojení. A pokud id, item_id, quantity, type ------------------------------- 1;1;2;PLUS 2;2;1;PLUS 3;1;1;PLUS 4;1;2;MINUS 5;1;1;PLUS 6;1;1;PLUS Zkuste na sqlfiddle.com nachystat data k testování a upravené dotazy, třeba pak bude potřeba jen drobné korekce. |
||
| Petr Ká Profil |
Kajman:
Schéma s nějakými daty jsem dal na SQL Fiddle |
||
| Kajman Profil |
#14 · Zasláno: 13. 4. 2016, 12:37:09
A s daty z [#12] to má tedy vrátit kolik řádků? 5 nebo 4? Má se seskupit 1. a 3. řádek?
|
||
| Petr Ká Profil |
Kajman:
z #12 by to mělo vrátit: 1;1;2;PLUS # nasledujici bude item_id=2 = neseskupujeme 2;2;1;PLUS # nasledujici bude item_id=1 = neseskupujeme 3;1;1;PLUS # nasledujici má sice stejne item_id, ale je typu MINUS = neseskupujeme 4;1;2;MINUS # nasledujici má sice stejne item_id, ale je typu PLUS = neseskupujeme 6;1;2;PLUS # SESKUPENI (id 5+6) = stejné item_id, a type = sečteme quantity |
||
| TomášK Profil |
Vyzkoušel jsem to a našel jsem akorát, že to nevrátilo poslední záznam, to je teď ošetřeno LEFT JOINem.
SELECT
item_id, group_id, reason, SUM(quantity)
FROM (
SELECT
t.item_id, t.quantity, t.reason, MIN(t2.id) AS group_id
FROM
warehouse_restaurant_stock_stats t
LEFT JOIN warehouse_restaurant_stock_stats t2 ON t2.id > t.id AND (t2.reason != t.reason OR t2.item_id != t.item_id)
GROUP BY t.id, t.item_id, t.quantity, t.reason
) extended_t
GROUP BY item_id, group_id, reason
ORDER BY group_id IS NULL, group_id, reasonPřidal jsem tam podmínku, aby se to chovalo podle nového zadání. |
||
| Kajman Profil |
TomášK:
V joinu by dle cursoru mělo být něco jako t2.user_id = t.user_id and t2.restaurant_id = t.restaurant_id.
|
||
| Kajman Profil |
#18 · Zasláno: 13. 4. 2016, 15:33:11
Petr Ká:
S uživ. proměnnými by ten upravený dotaz mohl být SELECT t.item_id,
Sum(t.quantity) quantity,
t.reason
FROM (SELECT x.*,
@blok := @blok + ( @posledni_type != x.reason
OR @posledni_item_id != x.item_id ) blok,
@posledni_type := x.reason pt,
@posledni_item_id := x.item_id pi
FROM warehouse_restaurant_stock_stats x
CROSS JOIN (SELECT @blok := 0,
@posledni_type := '',
@posledni_item_id := 0) v
WHERE x.user_id = 1
AND x.restaurant_id = 1
ORDER BY x.id) t
GROUP BY t.item_id,
t.reason,
t.blok
ORDER BY Min(t.id)Hodil by se index ALTER TABLE `warehouse_restaurant_stock_stats` ADD INDEX `user_rest`(`user_id`, `restaurant_id`, `id`); |
||
| Petr Ká Profil |
#19 · Zasláno: 13. 4. 2016, 17:52:48
To Kajmanovo řešení je úplně super :) A hlavně krásně rychlý.
TISÍCERÉ DĚKUJI :) |
||
|
Časová prodleva: 9 let
|
|||
0