Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Jak w temacie.
MS SQL Server 2012.
Nie mam koncepsji, jak uzyskać prawidłowy wynik w kolumnie Tekst, proszę o pomoc.

W tabeli występują pojęcia 'drewno', 'metal', 'metal i drewno', są one identyfikatorami definicjami słownikowych guid.

Jak sumować przykładowe dane, aby na końcu mieć wynik:
- suma kolumny Liczba (tu wiem, prosta funkcja SUM()),
- w kolumnie Tekst uzyskać w zależności od elementów:
-- jeżeli zawsze drewno, wynik drewno,
-- jeżeli zawsze metal, wynik metal,
--jeżeli występują mieszane drewno i metal, wynik 'metal i drewno',
-- jeżeli chociaż raz wystąpi 'metal i drewno', wynik 'metal i drewno'

Przykładowe dane:
Osoba Liczba Guid
Jan 7 drewno
Jan 8 drewno
Piotr 5 drewno
Piotr 2 metal
Piotr 10 drewno
Marcin 3 drewno
Marcin 10 metal i drewno
Tomek 4 metal
Tomek 3 metal
------------------------------------------
Oczekiwany wynik sumowania:
Osoba Liczba Tekst
Jan 15 drewno
Piotr 17 metal i drewno
Marcin 13 metal i drewno
Tomek 7 metal

Pozdrawiam,

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Pierwszym zapytaniem grupujesz po imie, materiał. Drugim grupujesz po imie i zliczasz. Jeżeli count = 1 to dany materiał, jeżeli nie to mieszane.

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Marcin P.:
MS SQL Server 2012.

> Jak sumować przykładowe dane, aby na końcu mieć wynik:

- suma kolumny Liczba (tu wiem, prosta funkcja SUM()),
- w kolumnie Tekst uzyskać w zależności od elementów:
-- jeżeli zawsze drewno, wynik drewno,
-- jeżeli zawsze metal, wynik metal,
--jeżeli występują mieszane drewno i metal, wynik 'metal i drewno',
-- jeżeli chociaż raz wystąpi 'metal i drewno', wynik 'metal i drewno'

Przykładowe dane:
Osoba Liczba Guid
Jan 7 drewno
Jan 8 drewno
Piotr 5 drewno
Piotr 2 metal
Piotr 10 drewno
Marcin 3 drewno
Marcin 10 metal i drewno
Tomek 4 metal
Tomek 3 metal
------------------------------------------
Oczekiwany wynik sumowania:
Osoba Liczba Tekst
Jan 15 drewno
Piotr 17 metal i drewno
Marcin 13 metal i drewno
Tomek 7 metal

Po pierwsze delikatnie mówiąc to bardzo "brzydka" ta twoja tabelka. Ale rozumiem że to przykład.

Pomijając walory estetyczne to bardzo zła praktyka wykorzystywanie powszechnie używanych terminów w innych znaczeniach niż te definicyjne. Tutaj mam na myśli twój Guid, który z GUID nie ma nic wspólnego. Oczywiście możesz sobie nazwać co chcesz jak chcesz więc z tą moją uwagę możesz zignorować. Jeśli jednak chcesz się czegoś nauczyć to proponuję ją przemyśleć i poszukać samodzielnie dlaczego tak napisałem.

Poniżej rozwiązanie (tabelkę z danymi nazwałem sobie _GLDrewnoMetal ).
1. Funkcja zwracająca osoby które spełniają twoje kryteria "metal i drewno"

Create Function fnOsobaDrewnoMetal () 

RETURNS TABLE As

	RETURN 

		Select Distinct Osoba From _GLDrewnoMetal 

			where [Guid] = 'metal i drewno'

		Union

		Select Distinct M.Osoba From _GLDrewnoMetal As M

		Join _GLDrewnoMetal As D ON M.Osoba = D.Osoba

			where M.[Guid] = 'metal' AND D.[Guid] = 'drewno'

2. Zapytanie dokonujące stosownych obliczeń dla Ad.1 i dla nie Ad.1 i scalające je w jedną tabelę.

Select Distinct MD.Osoba As Osoba, 

			    SUM(T1.Liczba) As Liczba, 

				'metal i drewno' As [Guid] 

From fnOsobaDrewnoMetal() MD 

Join _GLDrewnoMetal AS T1 On MD.Osoba = T1.Osoba

Group by MD.Osoba 

Having SUM(T1.Liczba) > 0

Union

Select Distinct T2.Osoba, 

				SUM(T2.Liczba) As Liczba,

				T2.[Guid]  

From _GLDrewnoMetal T2 

	Where T2.Osoba not in 

		  (Select Distinct Osoba From fnOsobaDrewnoMetal())

Group by T2.Osoba, T2.[Guid] 

Having SUM(T2.Liczba) > 0

Aby wszystko "trzymało się kupy" powinieneś tabelkę znormalizować i przepisać powyższe zgodnie z nowym projektem bazy. Ale to zrób sobie jako samodzielne ćwiczenie. Możesz jeszcze zbudować sobie inne zapytanie wykorzystując CTE czy też inaczej podzielić funkcjonalności bawiąc się optymalizacją.

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

A może po prostu na głupa w samym SQL

SELECT 

  imie, 

  (drewno + metal + metal_drewno) AS liczba,

  (CASE WHEN metal_drewno > 0 OR (drewno + metal) > 0 THEN 'drewno i metal'

       WHEN (drewno > 0 AND (metal + metal_drewno) = 0 THEN 'drewno'

       WHEN (metal > 0 AND (drewno + metal_drewno) = 0 THEN 'metal'

  ELSSE NULL END) AS guid

FROM (

  SELECT

    imie,

    sum((CASE WHEN guid = 'drewno' THEN liczba ELSE 0 END)) AS drewno,

    sum((CASE WHEN guid = 'metal' THEN liczba ELSE 0 END)) AS metal,

    sum((CASE WHEN guid = 'drewno i metal' THEN liczba ELSE 0 END)) AS metal_drewno

  ) FROM twoja_tabela GROUP BY imie

) AS ret

Przedmówca dobrze prawi od sprowadzeniu struktury do postaci normalnej.
Tak się zastanawiam "drewno i metal" kupiłem 10 . Z biznesowego punktu widzenia to sprzedano mi 2 metalu i 8 drewna czy może 5 metalu i 5 drewna? Pomijając fakt samej bazy bardzo zastanawia mnie trzymanie sumy dwóch tak różnych od siebie materiałów na jednym polu liczbowym. Ale tak jak przedmówca zakładam, że to przykład tylko...Ten post został edytowany przez Autora dnia 06.03.16 o godzinie 22:16

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Marcin M.:
A może po prostu na głupa w samym SQL

SELECT 

  imie, 

  (drewno + metal + metal_drewno) AS liczba,

  (CASE WHEN metal_drewno > 0 OR (drewno + metal) > 0 THEN 'drewno i metal' 

...

) AS ret

Ale to chyba nie MS SQL? ;-) Poniżej wersja z Case wykorzystująca wcześniej zdefiniowaną funkcję fnOsobaDrewnoMetal()

  SELECT

    g1.Osoba,

	Sum(g1.Liczba) As Liczba,

	g2.Guid

  FROM _GLDrewnoMetal g1 

  Join 

	(Select Distinct Osoba,

		  (CASE WHEN (Osoba IN (Select * from fnOsobaDrewnoMetal())) 

				THEN 'metal i drewno'

				ELSE [Guid] 

			END) As [Guid]

	 From _GLDrewnoMetal) as g2 On g2.Osoba = g1.Osoba

  GROUP BY g1.Osoba, g2.Guid

Tak się zastanawiam "drewno i metal" kupiłem 10 . Z biznesowego punktu widzenia to sprzedano mi 2 metalu i 8 drewna czy może 5 metalu i 5 drewna?

Nie sprzedano 10 czegoś co nie jest ani drewno ani metal lecz jest produktem zawierającym i drewno i metal, np: śrubokręt z drewnianą rączką, patelnia z drewnianym uchwytem, itd., itp., itd.

Pomijając fakt samej bazy bardzo zastanawia mnie trzymanie sumy dwóch tak różnych od siebie materiałów na jednym polu liczbowym.

Liczby są całkowite co sugeruje ilości ale przecież to mogą być też pieniądze więc np: wartość sprzedaży jest już istotnym wskaźnikiem biznesowym. Ponadto przy takich zestawieniach zazwyczaj porównuje się grupy asortymentowe, nie konkretne produkty a to całkowicie zmienia punkt odniesienia.

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

A składania standardowego języka sql bez żadnych udziwnień związanych z engiene bazy danych. Składnia powinna być jak najbardziej poprawna w MS: https://msdn.microsoft.com/pl-pl/library/ms181765(v=sql...
Dodatkowo nie udzielam konkretnej odpowiedzi tylko naprowadzam na rozwiązanie. Uważam, że nie trzeba generować funkcji tam gdzie nie jest to potrzebne.

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Marcin M.:
Składnia powinna być jak najbardziej poprawna w MS:

Co do idei ok, ale co do składni to jednak nie skompilujesz swojego przykładu, dlatego tak napisałem.

Dodatkowo nie udzielam konkretnej odpowiedzi tylko naprowadzam na rozwiązanie.

Ok, jak poprawić to zadziała. ;-)

Uważam, że nie trzeba generować funkcji tam gdzie nie jest to potrzebne.

Możesz rozwinąć definicję niepotrzebne? No i jeszcze jak ma się rozwiązanie na głupa vs rozwiązanie z zachowaniem odrobiny logiki, porządku? Co wybierzesz jeżeli prędkość wykonywania operacji jest akceptowalna w obu przypadkach?

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Przyjąłem sobie dwie zasady jak coś piszę. Podając SQL dbam aby był zgodny z SQL:1999 albo SQL:2003. Jeżeli odchodzę od tego to piszę w jakim engine bazy testowano.

Co do "niepotrzebne". Staram się unikać tworzenia dodatkowych obiektów jeżeli nie ma wyraźnej różnicy w potrzebnych zasobach czy prędkości.

Miałem kiedyś taką sytuację, gdzie dużo logiki było zawarte w funkcjach aż nie przyszedł kolo DBA i stwierdził, że zrobi porządki. Takowy wykosił wszystkie funkcje, które nie były użyte nigdzie w zapytaniach w kodzie pewnej aplikacji. Konsekwencje łatwo sobie wyobrazić...
Efektem tego jest unikanie przez mnie funkcji tam gdzie nie potrzeba ich używać a jak już funkcja jest tworzona to z kompletem komentarzy/objaśnień po co dlaczego i co wykonuje.

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Marcin M.:
Przyjąłem sobie dwie zasady jak coś piszę. .. Jeżeli odchodzę od tego to piszę w jakim engine bazy testowano.

Jesteś profesjonalistą. ;-)

Co do "niepotrzebne". Staram się unikać tworzenia dodatkowych obiektów jeżeli nie ma wyraźnej różnicy w potrzebnych zasobach czy prędkości.

Hmmm, .. ale w rzeczywistych projektach może ta zasada skutkować powstawaniem niezłych "potworków". Trudne w zrozumieniu logiki, trudne w testowaniu, dłużej powstające ale co jedno wypada uznać to zazwyczaj chyba zdecydowanie szybsze niż ich "logiczni" konkurenci. Tak jak i w przypadku niniejszej dyskusji gdzie twoja propozycja jest zdecydowanie szybsza od mojej. ;-)

Miałem kiedyś taką sytuację, gdzie dużo logiki było zawarte w funkcjach aż nie przyszedł kolo DBA i stwierdził, że zrobi porządki. Takowy wykosił wszystkie funkcje, które nie były użyte nigdzie w zapytaniach w kodzie pewnej aplikacji.

Mógł jeszcze wykasować te nieużywane przez ostatnie np: pół roku. :-( Cóż, zrobił bo mu zapewne kazano. Tyle tylko że w biznesie korzystanie z funkcji raz na rok, czy raz na kilka lat nie jest niczym niezwykłym. Na okoliczność błędów biznesu developer więc ma tylko jedno zabezpieczenie, kopia.

Efektem tego jest unikanie przez mnie funkcji tam gdzie nie potrzeba ich używać

Nie potrafię sobie jednoznacznie tego pojęcia zdefiniować. Można napisać kod bez funkcji? Będzie użyta tylko raz więc lepiej pokombinować tak aby bez niej napisać kod? OK, tak można napisać setki zapytań. Ale teraz co z sytuacją kiedy po pół roku, po roku, po .., baza ulega modyfikacji? Gdybyśmy mieli logicznie zdefiniowane funkcje i z nich korzystali to zapewne stanęło by na modyfikacji jednej, kilku, kilkunastu, a tak do sprawdzenia i poprawy są setki wcale o nie najprostszej logice bo przecież z tego zrezygnowaliśmy aby nie tworzyć dodatkowych obiektów. :-(

a jak już funkcja jest tworzona to z kompletem komentarzy/objaśnień po co dlaczego i co wykonuje.

Jak najbardziej rozsądnie. To chyba powinien być elementarz każdego programisty. Ale to też nie jest gwarancją czegokolwiek innego poza usprawiedliwieniem się nas samych przed samym sobą. Przecież zawsze może przyjść kolo, który nie ma czasu czytać i .. . :-(

No cóż, jak zawodzi kultura organizacji to mamy przechlapane.

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Marek Kubiś,
Tak, tabelka to tylko przykład.
Określenie GUID oznacza, że w kolumnie jest przechowywana faktycznie wartość GUID jako uniqueidentifier. Wartości 'drewno' i 'metal' wpisałem tylko dla czytelności tabelki :) Wiem, że logika i sensowność tego zapytania powala, ale klient życzy, więc muszę zrobić.
Zgodzę się z jedną z opinii, że użycie funkcji nie jest pożądane, chciałem tego uniknąć i napisać jedno zapytanie. Widzę jednak, że bez prequery i temp-tabeli chyba się nie obejdzie.
Obecnie zrobiłem to poprzez dwa zapytania zgodnie z pierwszą sugestią od Sławomira Brody: w temp-tabeli jest wynik grupowania po [Imie], [Material], w drugim grupowanie po [Imie] i zliczanie.
Nie podoba mi się to rozwiązanie, ale działa, wrócę do optymalizacji jak ogarnę resztę zadań.
Możliwe, że pierwsze podzapytanie przerzucę do indeksowanego View.

"SELECT imie, (drewno + metal + metal_drewno) AS liczba,[...]" nie zadziała, przecież możesz mieć np. 6 drewno i 1 metal.

"Z biznesowego punktu widzenia to sprzedano mi 2 metalu i 8 drewna czy może 5 metalu i 5 drewna? [...] - dla klienta nie ma znaczenia co zawiera suma, ważne jest czy w zbiorze wystąpił materiał jednolity (i jaki) czy mieszany (D&M).

Dziękuję za wszystkie podpowiedzi, przetestuję.

btw. Marcin Mackiewicz, ten DBA jeszcze u Was pracuje? :)

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Akurat ten DB to nie u mnie tylko w zaprzyjaźnionej firmie, której pomagam. U mnie by nie przeszedł przez pytania Dyrektora.

Zaproponowałem dodawanie bo nie wiedziałem. Zasugerowałem się nazewnictwem i przyjąłem że drewno > 0 i metal > 0 to to samo co drewno_metal > 0. Idąc za tym dalej wystarczyło posumować i tylko nazwę odpowiednią wstawić. Teraz wiem, że to osobne grupy to bym w taki sposób nie zrobił.

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Marcin Mackiewicz:
Zaproponowałem dodawanie bo nie wiedziałem. Zasugerowałem się nazewnictwem i przyjąłem że ... Teraz wiem, że to osobne grupy to bym w taki sposób nie zrobił.

Niepotrzebnie się wycofujesz bo twoja propozycja działa a zapytanie jest szybkie. Posłużenie się podstawieniem 0 sprawia, że i jakaś logika w tym jest, a że przy nawet najdrobniejszych zmianach reguł trzeba przemyśleć wszystko od początku to już inna sprawa.

Marcin Pachla:
Wiem, że logika i sensowność tego zapytania powala, ale klient życzy, więc muszę zrobić.

Aha .. Ale programiści zawsze pracują bo klient sobie życzy i zawsze mają wybór jak zrobić. Więc nie zdziw się jak jutro klient poprosi jeszcze o 'plastik' w zestawieniu i jego kombinacje z pozostałymi asortymentami. Ba pojutrze może przypomnieć mu się 'guma'. Nie mówiąc już o tym, że drewno bukowe różni się od dębowego, sosnowego czy innego. Tak samo jak różni się stal od aluminium czy złomu żelaznego, o metalach kolorowych nie zapominając. A jak klient np: wejdzie w ISO14001 to raporty o wielkości zanieczyszczenia środowiska zawierają jeszcze więcej 'dziwacznych' zestawień.

Wiem, wiem można wymyślać co by było gdyby i ma to bardzo ograniczony sens bo nie przewidzimy wszystkich zmian wymagań. Moje doświadczenie podpowiada mi jednak, że jak ktoś po bałaganiarsku pracuje z małymi rzeczami to między bajki wypada włożyć jego opowiadania, że jak będzie coś większego to on to uporządkuje i będzie lepiej niż jakby to Bill Gates z armią swoich programistów robił. Nie, jak w czymś małym jest bałagan to w czymś większym będzie 'odlot' na całego. Dlatego zachęcam do choćby odrobiny porządku w tym czymś małym, bo to naprawdę niewielki wysiłek, prawie taki sam, a różnica zauważalna. Pozostaje to jedynie prawie. A poza tym nic mi do tego.

Temat: SQL Server 2012 Jak zsumować liczby i różne rodzaje...

Marek K.:

Marcin Pachla:
Wiem, że logika i sensowność tego zapytania powala, ale klient życzy, więc muszę zrobić.

Aha .. Ale programiści zawsze pracują bo klient sobie życzy i zawsze mają wybór jak zrobić. Więc nie zdziw się jak jutro klient poprosi jeszcze o 'plastik' w zestawieniu i jego kombinacje z pozostałymi asortymentami. Ba pojutrze może przypomnieć mu się 'guma'.
Wiem, wiem można wymyślać co by było gdyby i ma to bardzo ograniczony sens bo nie przewidzimy wszystkich zmian wymagań. Moje doświadczenie podpowiada mi jednak, że jak ktoś po bałaganiarsku pracuje z małymi rzeczami to między bajki wypada włożyć jego opowiadania, że jak będzie coś większego to on to uporządkuje [..]

Wiem, wiem, prowizorki są najtrwalsze. Ale to nie prowizorka.
W obecnej formie zapytanie jest odporne na dodawanie nowych typów jak 'plastik' czy 'guma', Sprowadziłem je do postaci: suma typów różnych lub jednakowych, taki był wymóg.
Jak wspomniałeś, wszystko jest kwestią wyboru, w trójkącie projektowym - czas, zasoby, zakres. A czas właśnie powiedział, że się kończy.
Jak wspomniałem zapytanie działa w pełni poprawnie. Bez użycia funkcji, z użyciem jednej temp-tabeli. To że planuję do niego wrócić w przyszłości, wynika bardziej z dociekliwości i próby poszukania jeszcze bardziej optymalnego wykonania zadania. Ale to już w ramach czasu wolnego.