Autor Téma: dotaz nad mé síly  (Přečteno 1661 krát)

Offline Vladimir64

  • Nováček
  • *
  • Příspěvků: 34
  • Karma: 0
    • Verze Delphi: 10.2
dotaz nad mé síly
« kdy: 30-07-2023, 18:58:37 »
Dobrý den, potřebuji vypočítat produkci jednotlivých oddělení po jednotlivých dnech, v tabulce jsou uvedena čísla kusů (jedná se o produkci textilního závodu). Čísla jsou unikátní. Dále čísla strojů, délku kusů v metrech, datum a čas kdy byl kus zpracován.

piece - číslo kusu ve formátu varchar(15)
mydate - datum kdy byl kus zpracován ve formátu date
mytime - čas kdy byl kus zpracováván ve formátu time
m - délka kusu v metrech ve formátu float

dále @specific_date je datum který zadávám na začátku dotazu a je to datum za který počítám produkci


Oddělení které počítám má několik strojů ale některé z nich mají více částí. V případě, že má stroj víc částí je v tabulce i víc záznamů. Každý záznam má ale stejné číslo stroje. Je vcelku jednoduché zjistit jestli na tom stroji ten kus už jednou byl a pokud ano podruhé ho nezapočítávat (nechci mít v produkci trojnásobné metry, pokud jeden a ten stejný kus prochází 3 částmi stejného stroje). Problém je v tom, že v technologických postupech existuje i varianta, kdy kus skutečně prochází strojem vícekrát (včetně jeho jednotlivých částí) Chci pro rozlišení toho jestli se jedná o jeden průchod (i když více částmi) nebo vícenásobný průchod, využít čas kdy daný kus byl zpracováván. Pokud tedy je ten kus na stejném stroji během jedné hodiny,nezapočítávat, je to v rámci jednoho průchodu strojem, pokud ale je časový rozdíl větší než 1 hodina započítávat. V tom případě se jedná skutečně o vícenásobný průchod strojem.
Vytvořil jsem dotaz kde vytvářím přechodnou tabulku temp_results, kam ukládám do jednotlivých řádků výsledky jednotlivých oddělení, nakonec to vyexportuji v textovém souboru a přechodnou tabulku smažu. Všechno funguje jen s tím jedním oddělením zápasím už celý den.

toto nepracuje korektně (na oddělení jsou 3 stroje s čísly 1001, 1002 a 1005) jen stroje 1001 a 1002 mají víc částí.

INSERT INTO temp_results (row_data)
SELECT CONCAT(@custom_text6, ';', REPLACE(FORMAT(SUM(m), 0), ',', ' '))
FROM (
    SELECT a.piece,
           CASE
               WHEN a.machine = '1005' THEN SUM(a.m)
               WHEN a.machine IN ('1001', '1002') THEN
                    CASE
                        WHEN (
                            SELECT TIMESTAMPDIFF(HOUR, MAX(CONCAT(b.mydate, ' ', b.mytime)), MAX(CONCAT(a.mydate, ' ', a.mytime)))
                            FROM av50 AS b
                            WHERE b.piece = a.piece AND b.machine IN ('1001', '1002') AND DATE(b.mydate) < @specific_date
                        ) > 1 THEN SUM(a.m)
                        ELSE 0
                    END
               ELSE 0
           END AS m
    FROM av50 AS a
    WHERE DATE(a.mydate) = @specific_date AND a.machine IN ('1001', '1002', '1005')
    GROUP BY a.piece, a.machine
) AS subquery
GROUP BY DATE(@specific_date);

najde se někdo kdo to dá na první dobrou? Snažil bych se dál, jen čas kdy to musím mít hotové se neúprosně blíží.

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 442
  • Karma: 6
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:dotaz nad mé síly
« Odpověď #1 kdy: 31-07-2023, 12:38:07 »
jen tak na první pohled tam vidím problem:
Kód: [Vybrat]
CASE
                        WHEN (
                            SELECT TIMESTAMPDIFF(HOUR, MAX(CONCAT(b.mydate, ' ', b.mytime)), MAX(CONCAT(a.mydate, ' ', a.mytime)))
                            FROM av50 AS b
                            WHERE b.piece = a.piece AND b.machine IN ('1001', '1002') AND DATE(b.mydate) < @specific_date
                        ) > 1 THEN SUM(a.m)
                        ELSE 0
                    END

Kdyz je rozdil vic jak hodinu, tak spocitas, jinak davas natvrdo nulu a pritom bys mel pocitat jen ten jeden pruchod

Druhy problem, v tom subselectu mas DATE(b.mydate) < @specific_date a podle mne by tam melo byt DATE(b.mydate) = @specific_date

« Poslední změna: 31-07-2023, 12:40:22 od Jan Fiala »

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 442
  • Karma: 6
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:dotaz nad mé síly
« Odpověď #2 kdy: 31-07-2023, 12:54:17 »
Zkus na to jit z druhe strany. Sectes vse a odectes od toho pripady, kdy se vejdes do hodiny. Doufam, ze MySQL umi Left Outer Join. Ten se napoji jen, pokud je splnena podminka, jinak vraci null

Kód: [Vybrat]
select a.machine, a,piece, sum(a.m - IfNull(b.m, 0))
from av50 as a
left outer join av50 as b on a.piece = b.piece and (a.machine <> '1005') and  TIMESTAMPDIFF(HOUR, MAX(CONCAT(b.mydate, ' ', b.mytime)), MAX(CONCAT(a.mydate, ' ', a.mytime))) < 1   
where DATE(a.mydate) = @specific_date AND a.machine IN ('1001', '1002', '1005')
GROUP BY a.piece, a.machine

Omlouvam se, pisu to z hlavy

Offline Vladimir64

  • Nováček
  • *
  • Příspěvků: 34
  • Karma: 0
    • Verze Delphi: 10.2
Re:dotaz nad mé síly
« Odpověď #3 kdy: 31-07-2023, 17:03:14 »
proč se omlouváš? Kéž bych já dokázal napsat složitý dotaz jen tak z hlavy. Já ho pracně skládám po částech, často způsobem pokus - omyl. Snad mě ale omlouvá, že nejsem IT profesionál jako mnozí na tomto fóru a s databázemi si zatím moc netykám. Stáhnu ze serveru aktuální data a jdu to vyzkoušet. Zatím díky. Dám vědět jak jsem dopadl.

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 442
  • Karma: 6
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:dotaz nad mé síly
« Odpověď #4 kdy: 31-07-2023, 17:34:00 »
Jeste z te join podminky vyluc pripady, kdy je cas stejny (pridej and a.cas <> b.cas), at ti tam neco zustane

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7392
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:dotaz nad mé síly
« Odpověď #5 kdy: 01-08-2023, 08:30:45 »
O MySQL neviem nič. Uvedený SQL a jeho popis je pre mňa veľmi zložitý :o . Takže to bude len o tom, ako zvyknem postupovať. Možno Ti to pomôže.
Ak potrebujem pracovať len s časťou údajov, tak v takom prípade som si obľúbil konštrukciu WITH XX AS (SELECT ...)...
Výhody ktoré vidím ja:
  • samostatne si pripravím subselect-y - viem si skontrolovať ich výsledky
  • základný kód je omnoho čitateľnejší. Lebo napr. namiesto INNER JOIN (SELECT ...) mám jednoduché INNER JOIN XX
Bez Visual Query Builder sa nepohnem ;)
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Vladimir64

  • Nováček
  • *
  • Příspěvků: 34
  • Karma: 0
    • Verze Delphi: 10.2
Re:dotaz nad mé síly
« Odpověď #6 kdy: 01-08-2023, 21:09:05 »
zatím to nejde. Zkouším opravit svůj původní dotaz podle první připomínky, ale vypadá to, že budu opravdu muset zkusit tu druhou navrhovanou konstrukci. Sečíst všechny metry a odečítat ty které splní uvedenou podmínku. Musím to ale přerušit, od 1.8. musím reportovat výrobu a musím dokončit vzhled reportu a jeho export. Dokud to nezvládnu budu muset toto jediné oddělení dopočítávat ručně. Ostatní jsou ok. To na pár dní není žádná tragédie, ale nechtěl bych to dělat dlouho. Budu pokračovat v tomto vlákně, jakmile to dořeším

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 442
  • Karma: 6
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:dotaz nad mé síly
« Odpověď #7 kdy: 02-08-2023, 06:49:57 »
Odlaď si tu druhou část, abys měl jistotu, že tam máš to, co potřebuješ, tzn. napřed to vyber bez GROUP BY a vedle sebe v dotazu postav vysledky z A i B, abys vedel, co se ti bude sčítat. Něco jako

Kód: [Vybrat]
select a.machine, a,piece, a.m, a.mydate, a.mytime, b.machine, b.piece, b.m, b.mytime
from av50 as a
left outer join av50 as b on a.piece = b.piece and (a.machine <> '1005') and  TIMESTAMPDIFF(HOUR, MAX(CONCAT(b.mydate, ' ', b.mytime)), MAX(CONCAT(a.mydate, ' ', a.mytime))) < 1  and (a.mytime <> b.mytime)
where DATE(a.mydate) = @specific_date AND a.machine IN ('1001', '1002', '1005')

Měl přehledně vidět z A vše a z B pouze to, čeho se chceš zbavit.

Offline Vladimir64

  • Nováček
  • *
  • Příspěvků: 34
  • Karma: 0
    • Verze Delphi: 10.2
Re:dotaz nad mé síly
« Odpověď #8 kdy: 08-06-2024, 22:37:36 »
po delší době se vracím ke staršímu tématu. Mám to už delší dobu vyřešené. Řešení není moje, nicméně funguje velice dobře. Nejdříve si vytvořím tabulku s čísly kusů které se zpracovávaly na sledovaných strojích ve sledovaném dni nebo dnech. Pokud se kus vyskytoval kdykoliv v minulosti přidám ho do tabulky také. K tomu do vytvořené tabulky dám i údaje o délce kusu a datumu a času zpracování. Poté si tabulku seřadím podle čísel kusu a jako sekundární kritérium řazení si dám datum a čas zpracování. No a nakonec si udělám rozdíl času mezi záznamy v tabulce a kus započítám pouze pokud je čas od zpracování delší než jedna hodina. Nebudu sem dávat sql kód, pouze pokud by měl někdo zájem tak ať napíše. Dlouho jsem dělal reporty tak, že jsem zadával složený sql dotaz přímo v mysql (používám k přístupu k mysql XAMPP), ale minulý měsíc jsem si řekl, že je čas si to zefektivnit a udělat si v delphi nějakého mysql klienta. Pracuji na tom, mám hotové reporty jednotlivých strojů po dnech nebo po zadaných obdobích, kvalitu, pracuji na kvalitě podle obsluhy a poté to ještě doplním nějakým reportem produktivity. Nicméně můj dotaz teď směřuje k přístupu k vytváření takového klienta. Mám zakoupené komponenty MYDAC a pro sql dotazy používám komponenty myquery. Celý klientský program bude mít cca do 50 dotazů. Mnohé z nich jsou dost obsáhlé. Zajímá mě jaký přístup je "čistější"? Umístit do projektu 50 komponent MyQuery, nebo přiřazovat sql jedné komponentě MyQuery při obsluze jednotlivých událostí? Při importu dat, při exportu dat, při získání výsledků produkce jednotlivých strojů atd. atd.? Jaké řešení byste volili a proč?

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7392
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:dotaz nad mé síly
« Odpověď #9 kdy: 09-06-2024, 11:24:56 »
Citace
Mnohé z nich jsou dost obsáhlé. Zajímá mě jaký přístup je "čistější"? Umístit do projektu 50 komponent MyQuery, nebo přiřazovat sql jedné komponentě MyQuery při obsluze jednotlivých událostí? Při importu dat, při exportu dat, při získání výsledků produkce jednotlivých strojů atd. atd.? Jaké řešení byste volili a proč?
Ja dávam prednosť tomuto postupu:
  • vytvoriť MyQuery - je to blesková akcia. Nezdržiava to
  • vložiť doň SQL text
  • spustiť MyQuery
  • zničiť MyQuery
Dôvody:
  • každé Query mám na jedinom mieste
  • vidím presne jeho životnosť
  • tým nedochádza k úniku pamäte
  • väčšinou to mám zabalené v jednej rutine
  • takže hlavný blok programu je čo najkratší a jasne čitateľný
Samozrejme, že používam aj iné scenáre. Ale to vždy záleží od konkrétnej situácie.
U mňa neexistuje rutina dlhšia ako 50 riadkov.
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Vladimir64

  • Nováček
  • *
  • Příspěvků: 34
  • Karma: 0
    • Verze Delphi: 10.2
Re:dotaz nad mé síly
« Odpověď #10 kdy: 10-06-2024, 19:37:47 »
to chápu, na druhou stranu i přístup co sql dotaz to vlastní Query má také své výhody. Nevizuální komponenty neumisťuji na hlavní formulář, takže mám všechny Query hezky pohromadě. Každé Query si pojmenuji tak, že je z názvu jasné jaký sql dotaz obsahuje. V hlavním programu mám jen Open a Close, případně nějaké parametry pro Query. Asi je jasné, že tento přístup je nerealizovatelný pro programy kde je sql dotazů příliš moc, ale kolik je takových programů? Pokud by těch dotazů bylo hodně, asi bych roztřídil Query komponenty do skupin a zase jich v programu využil více. Naschvál jsem si díval, jaký dopad má počet query komponent na velikost spustitelného souboru. Zanedbatelný. Ohledně využití paměti to nedokážu posoudit. Vzhledem k rychlosti kterým jsou dotazy zpracovávány, jsem ale zřejmě na žádný limit nenarazil.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7392
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:dotaz nad mé síly
« Odpověď #11 kdy: 12-06-2024, 20:45:18 »

Na tomto fóre mi odporúčali, že čo sa dá vytvoriť dynamicky, tak to tak treba urobiť. Držím sa ich odporúčaní a všade som to tak urobil. Ale to nie je dôvod, prečo teraz píšem.


Podľa odporúčaní MS aj tu na fóre, treba použivať predvolené adresáre OS. Pre údaje to je /Data... a pre aplikácie /Program Files..., /Program Files (x86)... alebo /Users/UserName...Problém je v tom, že adresáre si môže voliť užívateľ (aspoň by to tak malo byť) a menia sa podľa druhu OS!!! Ak máš údaje pokope s aplikáciou, tak užívateľ si to môže dať na iný disk než C:. To je môj prípad.
Z toho vyplýva, že ich treba zistiť dynamicky pri spustení programu a nastaviť ich pre všetky TQuery, TTable. Iné datasety nepoužívam. Ja som nenašiel spôsob, ako dynamicky načitať TQuery či TTable.
Druhý problém je, že ak zmeníš SQL text, tak musíš zktualizovať zoznam polí.


Pri dynamickej tvorbe TQuery toto všetko odpadá. Osobne mám vytvorené tri funkcie:
NewQuery(Name: string; Transaction: TFDTransaction ... alebo iný typ);
OpenQuery(Transaction: TFDTransaction); Ak spúšťam Query prvý krát, tak použijem Open. Ak zmením hodnotu čo len jedného parametra alebo to spúšťam opakovane, tak použijem Refresh.
ExecQuery(Transaction: TFDTransaction);


Používam jediné TConnection, takže ho daným funkciám neposielam.


Toto sú u/pre mňa veľmi silné dôvody na dynamické vytváranie TQuery. Samozrejme existujú výnimky. Používam TMS komponenty a tam to je napríklad TDBLooukupComboBox (presný názov si nepamätám).


Som zvedavý na Tvoj postoj k takému pohľadu na problematiku.
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Vladimir64

  • Nováček
  • *
  • Příspěvků: 34
  • Karma: 0
    • Verze Delphi: 10.2
Re:dotaz nad mé síly
« Odpověď #12 kdy: 13-06-2024, 15:58:14 »
původní dotaz byl, jestli je lepší při daném počtu sql dotazů (neměnných) lepší využít co sql dotaz to vlastní MyQuery komponenta, nebo sql dotazy dynamicky přiřazovat jedné MyQuery komponentě. Ty jsi to rozšířil i na dynamické vytváření a ničení MyQuery. Myslím si, že se tím vzdáváš části výhod, které přináší objektově orientované programování. Dynamické vytváření a ničení MyQuery, znamená také opakovaně iniciovat parametry. Mluvíme o specifické aplikaci, která má neměnné sql dotazy (i když jich bude tak do padesáti), mění se jen parametry dotazu. Některé argumenty vztahující se k paměti a rychlosti mají čím dál menší význam a u aplikací tohoto typu zanedbatelný. Využitím předdefinovaných komponent přenechávám část programátorské práce především vztahující se k životnímu cyklu komponenty a správě paměti dané komponentě. Ještě bych zmínil jednu pro mě velice důležitou vlastnost mého přístupu. Sql dotazy si nepamatuji, natož 50 sql dotazů. V aplikaci mám nastavené dva režimy. V prvním režimu se provádí vyvolaný sql dotaz. V druhém režimu se dotaz neprovádí ale zobrazí.
Memo2.Lines.Text := QueryForm.MyQueryTkalcovna.SQL.Text;
Pokud si teda chci sql dotaz prohlídnout, zkontrolovat, oživit, abych si ujasnil, co výpočet vyjadřuje, nemusím se "hrabat" ve zdrojovém kódu.
Myslím si, že můj přístup je jasnější, kód čitelnější, méně náchylný chybám. Všechny MyQuery mám na jednom formuláři (Delphi při jeho zničení automaticky uvolní paměť pro všechny MyQuery). Mám je jasně pojmenované (na nevizuálním formuláři dokonce i roztříděné do skupin), všechny sql dotazy mám na jednom místě, použitelné v jakékoliv části programu, u každé komponenty MyQuery mám jasně uvedené parametry, které dotaz požaduje. Protože některé parametry jsou společné pro více dotazů (například od.. do jsou společné parametry pro všechny dotazy) můžu je inicializovat jen jednou.
Tak to jsou zase pro mě silné argumenty pro využití více MyQuery.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7392
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:dotaz nad mé síly
« Odpověď #13 kdy: 13-06-2024, 17:17:00 »
Ak Ti vyhovuje súčasný stav, tak sa niet o čom baviť. Mimochodom, 50 dopytov nie je veľa.
Ja mnohé SQL texty vytváram dynamicky. Podľa volieb užívateľa. To je zásadný rozdiel oproti Tvojim statickým textom.
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Jirka

  • Hrdina
  • ****
  • Příspěvků: 497
  • Karma: 9
    • Verze Delphi: XE2
Re:dotaz nad mé síly
« Odpověď #14 kdy: 14-06-2024, 08:19:30 »
původní dotaz byl, jestli je lepší při daném počtu sql dotazů (neměnných) lepší využít co sql dotaz to vlastní MyQuery komponenta, nebo sql dotazy dynamicky přiřazovat jedné MyQuery komponentě.

Pokud je výstup fixní tj. není požadavek na různé druhy výstupu preferoval bych mít jednu MyQuery komponentu na DataModulu  + volací proceduru  která bude mít vstupní parametr
typu record   například
Kód: [Vybrat]
  rSQL_String  = packed record
  SQL_1:String ;
  SQL_WHERE:String;
  SQL_ORDER:String;

  end;

a pak to volat z požadovaných míst ..