Raport z bazy MsSQL z danymi ułożonymi wg id

Odpowiedz Nowy wątek
2018-11-27 11:55
Trzeźwy Pomidor
0

Witajcie,
Mam bazę gdzie dla każego ID przypisane są pewne parametry


id | parametr | wartość
1 | nazwa | lalala
1 | waga | 10
1 | cena | 15
1 | kolor | niebieski
1 |rok | 2018
2 | nazwa | tytyty
2 | wysokość | 15
2 | cena | 8
2 | kolor | niebieski
2 | data przeglądu | 2015-01-12

itd. itp. parametrów w bród i dla jednego id występują, ale dla innego już nie muszą.
Jak z tak ułożonych danych wyraportować - pokaż mi wszystkie produkty (numery id, zakładamy, że id to oddzielny produkt o możliwej nieskończonej ilości parametrów), gdzie cena = 10 AND kolor = 'niebieski'

Pozostało 580 znaków

2018-11-27 12:25
0
select id from tabela where (parametr = 'cena' and wartosc = 10) or (parametr = 'cena' and kolor = 'niebieski') group by id having count(id) = 2

Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.

Pozostało 580 znaków

2018-11-27 13:05
0

Osobiście preferuję taki zapis:

SELECT id FROM tabela WHERE (parametr, wartosc ) IN (('cena', '10'), ('kolor', 'niebieski')) GROUP BY id HAVING Count(DISTINCT parametr)=1
ale z tego co wiem to nie wszystkie bazy go dopuszczają - abrakadaber 2018-11-27 15:58
@abrakadaber: Masz na myśli Accessa? :) on chyba nie dopuszcza DISTINCT w Count... :) A stosuję taki zapis, bo (jak jeszcze ładnbie sformatuję zapytanie) wszystko ładnie widać... - Marcin.Miga 2018-11-27 16:01
Pomyłka w SQl - powinno być =2 zamiast =1 - Marcin.Miga 2018-11-27 16:02
TSQL taki zapis in spowoduje błąd: An expression of non-boolean type specified in a context where a condition is expected, near ','. - Panczo 2018-11-27 16:18
@Panczo - faktycznie... A nawet MySQL to ma :) A wydawało mi się, że już kiedys to sprawdzxałem... - Marcin.Miga 2018-11-27 16:25

Pozostało 580 znaków

2018-11-27 14:04
0

I jeszcze jedna propozycja:

select a.id from tabela a where a.parametr='kolor' and a.value='niebieski' and exists (select 1 from tabela b where b.id=a.id and b.parametr='cena' and b.wartosc='10');

albo (doczytałem, że to MSSQL a nie MySQL, więc intersect powinno być):

select id from tabela where parametr='kolor' and wartosc='niebieski'
intersect 
select id from tabela where parametr='cena' and wartosc='10'
edytowany 1x, ostatnio: yarel, 2018-11-27 14:06
tylko przy 10 parametrach zaczyna się to robić trochę brzydkie :p - abrakadaber 2018-11-27 15:59
To prawda, ale czasem korci użyć jakiegoś niszowego operatora :D - yarel 2018-11-27 16:24

Pozostało 580 znaków

2018-11-27 16:01
0

taka uwaga na koniec odnośnie mojego i @Marcin.Miga kodu - jeśli będziesz miał zdublowane pary parametr-wartość dla danego id po których szukasz to dostaniesz błędne wyniki


Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.
edytowany 2x, ostatnio: abrakadaber, 2018-11-27 16:03
O, wypraszam sobie.... JA mam DISTINCTa :) - Marcin.Miga 2018-11-27 16:01
a masz rację - już Cię wykreślam :p - abrakadaber 2018-11-27 16:02

Pozostało 580 znaków

2018-11-27 16:05
0

Nie wiem czy Cie zrozumialem, ale chcesz transponowac wiersze w kolumny? Jesli tak poczytaj o operacji GROUP_CONCAT, ihmo wybieranie kazdej kolumny w podzapytaniu to nie jest najwydajniesze:

https://dba.stackexchange.com[...]vert-rows-as-columns-in-mysql

SELECT * FROM tabela WHERE id IN (SELECT t2.id FROM tabela AS t2 where t2.parametr='cena' and t2.wartosc > 10)
edytowany 3x, ostatnio: daniel1302, 2018-11-27 16:10
źle zrozumiałeś - abrakadaber 2018-11-27 16:08
Widze :D Przeczytalem po lebkach Dziekowac :) - daniel1302 2018-11-27 16:10

Pozostało 580 znaków

2018-11-27 16:46
0

Nienawidzę modelu EAV ;)
Jak muszę to posiłkuje się w MSSQL typem xml, wtedy jest łatwiej:

--dane
select 1 id,
convert(xml,N'<root>
    <nazwa>lalala</nazwa>
    <waga>10</waga>
    <cena>15</cena>
    <kolor>niebieski</kolor>
    <rok>2018</rok>
</root>') atr
into a

insert into a
select 1 id,
convert(xml,N'<root>
    <nazwa>lalala</nazwa>
    <waga>10</waga>
    <cena>1</cena>
    <kolor>niebieski</kolor>
    <rok>2018</rok>
</root>') atr

I proste zapytanie:


select * 
from a
where 
    atr.value('(//root/waga)[1]','integer')=10
    and atr.value('(//root/cena)[1]','integer')=15

wtedy nawet jak pytasz o nieistniejące atrybuty to nie musisz sprawdzać czy są, bo takie coś nie zwróci błędu:


select * 
from a
where 
    atr.value('(//root/nieistniejącyatrybut)[1]','integer')=10
edytowany 1x, ostatnio: Panczo, 2018-11-27 18:28

Pozostało 580 znaków

2018-11-27 18:44
0

To jeszcze dodam, że xmla można wykorzystać w twoim przypadku:

with dane as (
select
   id
   ,(select 
        cast('<' + lower(replace(parametr,' ','_')) + '>' + wartosc + '</'+lower(replace(parametr,' ','_')) + '>' as xml)
    from a x 
    where x.id=b.id
    for xml path(''), root('root'), type) atr
from 
    (select distinct id from a) as b
)

SELECT * 
FROM dane
WHERE 
    atr.value('(//root/waga)[1]','integer')=10
    AND atr.value('(//root/kolor)[1]','varchar(10)')='niebieski'

Wtedy też jest łatwiej w przypadku np. takim: znajdż te z kolorem niebieskim i zdefiniowaną datą przeglądu:

SELECT * 
FROM dane
WHERE 
    atr.exist('(//root/data_przeglądu)')=1
    AND atr.value('(//root/kolor)[1]','varchar(10)')='niebieski'
a jak to wygląda wydajnościowo? - abrakadaber 2018-11-27 21:00

Pozostało 580 znaków

2018-11-27 22:15
2

@abrakadaber: Nie badałem ;) Ale też nie odczułem zwolnień z tego powodu. Możliwe, że budowanie zapytań byłoby szybsze, ale ja cenię sobie wygodę...

W każdym razie piszę o sytuacji gdzie jest to zrobione zgodnie ze sztuką, a to wymaga troszkę wiedzy jak działa typ XML w MS SQL,

Więc od początku, nie wystarczy stworzyć tabeli z kolumną XML i zapomnieć o sprawie, bo to proszenie się o kłopoty ;)

Tworzymy xsd i dodajemy do bazy (CREATE XML SCHEMA COLLECTION), następnie kolumna w bazie jest typed, to w głownej mierze sprawia że xml nie jest traktowany jako ciąg znaków, a (w uproszczeniu) przyspiesza wyszukiwanie, ponieważ znane są typy danych elementów / atrybutów itd. To co prawda wpływa na insert ale wspiera select.

Dodatkowo tworzymy index na kolumnie xml, sam MS SQL trzyma xml-a jako BLOB co wpływa na to że podczas wykonywania zapytania czytana jest cała zawartość., Założenie indexu powoduje, że do tego bloba tworzone są wiersze w indexie zawierające informacje o ścieżce, typie, nazwie wartości itd. co znacząco przyspiesza zapytanie i widzimy oczekiwany index seek

To oczywiście w dużym skrócie i uproszczeniu.

Dzięki za wytłumaczenie - z mssqlem mam mało do czynienia a trochę mnie męczyło szukanie w xmlu bez indeksów :) - abrakadaber 2018-11-28 09:14

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0