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
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
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
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
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, type

Př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
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
by nesel?

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
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
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
má vrátit 5 řádků (a tedy neplatí zadání „seskupit záznamy dle "item_id" do změny "type"“ a hlídá se změna obojího) je potřeba logiku v obou dotazech změnit.

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
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, reason

Př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
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
To Kajmanovo řešení je úplně super :) A hlavně krásně rychlý.

TISÍCERÉ DĚKUJI :)

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: