Szkolenie
Moduł
Używanie wbudowanych funkcji i funkcji GROUP BY w języku Transact-SQL - Training
Używanie wbudowanych funkcji i funkcji GROUP BY w języku Transact-SQL
Ta przeglądarka nie jest już obsługiwana.
Przejdź na przeglądarkę Microsoft Edge, aby korzystać z najnowszych funkcji, aktualizacji zabezpieczeń i pomocy technicznej.
W dodatku Power Query można grupować wartości w różnych wierszach w jedną wartość, grupując wiersze według wartości w co najmniej jednej kolumnie. Można wybrać spośród dwóch typów operacji grupowania:
Grupowania kolumn.
Grupowania wierszy.
Na potrzeby tego samouczka używasz poniższej przykładowej tabeli.
Zrzut ekranu przedstawiający tabelę z kolumnami Year (2020), Country (USA, Panama lub Canada), Product (Shirt lub Shorts), Sales channel (Online lub Reseller) i Units (różne wartości z zakresu od 55 do 7500)
Przycisk Grupuj według można znaleźć w trzech miejscach:
Na karcie Narzędzia główne w grupie Przekształć.
Na karcie Przekształć w grupie Tabela.
W menu skrótów po kliknięciu prawym przyciskiem myszy, aby wybrać kolumny.
W tym przykładzie twoim celem jest podsumowanie łącznej liczby sprzedanych jednostek na poziomie kraju i kanału sprzedaży. Kolumny Country (Kraj) i Sales Channel (Kanał sprzedaży) służą do wykonywania operacji grupowania według.
Ta operacja zawiera poniższą tabelę.
Dzięki funkcji Grupuj według dostępne operacje można podzielić na dwa sposoby:
W poniższej tabeli opisano każdą z tych operacji.
Nazwa operacji | Kategoria | opis |
---|---|---|
Sum | Operacja kolumny | Sumuje wszystkie wartości z kolumny |
Średnia | Operacja kolumny | Oblicza średnią wartość z kolumny |
Mediana | Operacja kolumny | Oblicza medianę z kolumny |
Min | Operacja kolumny | Oblicza minimalną wartość z kolumny |
Maksimum | Operacja kolumny | Oblicza maksymalną wartość z kolumny |
Percentyl | Operacja kolumny | Oblicza percentyl przy użyciu wartości wejściowej od 0 do 100 z kolumny |
Zlicz unikatowe wartości | Operacja kolumny | Oblicza liczbę unikatowych wartości z kolumny |
Zlicz wiersze | Operacja wiersza | Oblicza całkowitą liczbę wierszy z danej grupy |
Zlicz unikatowe wiersze | Operacja wiersza | Oblicza liczbę odrębnych wierszy z danej grupy |
Wszystkie wiersze | Operacja wiersza | Zwraca wszystkie zgrupowane wiersze w wartości tabeli bez agregacji |
Uwaga
Liczba unikatowych wartości i operacji percentylu są dostępne tylko w usłudze Power Query Online.
Począwszy od oryginalnego przykładu, w tym przykładzie utworzysz kolumnę zawierającą łączną liczbę jednostek i dwie inne kolumny, które dają nazwę i jednostki sprzedawane dla produktu o najwyższej wydajności, podsumowane na poziomie kraju i kanału sprzedaży.
Użyj następujących kolumn jako Grupuj według kolumn:
Utwórz dwie nowe kolumny, wykonując następujące kroki:
Po zakończeniu tej operacji zwróć uwagę, że kolumna Products zawiera wartości [Table] wewnątrz każdej komórki. Każda wartość [Tabela] zawiera wszystkie wiersze pogrupowane według kolumn Country (Kraj ) i Sales Channel (Kanał sprzedaży) z oryginalnej tabeli. W komórce możesz wybrać biały znak, aby wyświetlić podgląd zawartości tabeli w dolnej części okna dialogowego.
Uwaga
Okienko podglądu szczegółów może nie zawierać wszystkich wierszy używanych do operacji grupowania. Możesz wybrać wartość [Tabela], aby wyświetlić wszystkie wiersze odnoszące się do odpowiedniej operacji grupowania.
Następnie należy wyodrębnić wiersz o najwyższej wartości w kolumnie Units (Jednostki) tabel w nowej kolumnie Products (Produkty) i wywołać tę nową kolumnę Produkt o najwyższej wydajności.
Nowa kolumna Products (Produkty) z wartościami [Table] umożliwia utworzenie nowej kolumny niestandardowej, przechodząc do karty Dodaj kolumnę na wstążce i wybierając pozycję Kolumna niestandardowa z grupy Ogólne.
Nadaj nowej kolumnie nazwę top performer product. Wprowadź formułę Table.Max([Products], "Units" )
w obszarze Formuła kolumny niestandardowej.
Wynik tej formuły tworzy nową kolumnę z wartościami [Record]. Te wartości rekordów są zasadniczo tabelą z tylko jednym wierszem. Te rekordy zawierają wiersz z maksymalną wartością kolumny Units (Jednostki) każdej wartości [Table] w kolumnie Products (Produkty).
Po wykonaniu tej nowej kolumny produktu o najwyższej wydajności zawierającej wartości [Rekord] możesz wybrać ikonę rozwijania, wybrać pola Product and Units (Produkty i jednostki), a następnie wybrać przycisk OK.
Po usunięciu kolumny Products i ustawieniu typu danych dla obu nowo rozwiniętych kolumn wynik będzie podobny do poniższego obrazu.
Uwaga
Poniższa funkcja jest dostępna tylko w usłudze Power Query Online.
Aby zademonstrować sposób "grupowania rozmytego", rozważ przykładową tabelę pokazaną na poniższej ilustracji.
Celem grupowania rozmytego jest wykonywanie operacji grupowania według, która używa przybliżonego algorytmu dopasowania dla ciągów tekstowych. Dodatek Power Query używa algorytmu podobieństwa Jaccard do mierzenia podobieństwa między parami wystąpień. Następnie stosuje klastry hierarchiczne agglomeracyjne do grupowania wystąpień razem. Na poniższej ilustracji przedstawiono oczekiwane dane wyjściowe, w których tabela jest pogrupowana według kolumny Person .
Aby wykonać grupowanie rozmyte, wykonaj te same kroki opisane wcześniej w tym artykule. Jedyną różnicą jest to, że tym razem w oknie dialogowym Grupuj według zaznacz pole wyboru Użyj grupowania rozmytego.
Dla każdej grupy wierszy dodatek Power Query wybiera najczęściej używane wystąpienie jako wystąpienie "kanoniczne". Jeśli wystąpi wiele wystąpień z tą samą częstotliwością, dodatek Power Query wybierze pierwszy z nich. Po wybraniu przycisku OK w oknie dialogowym Grupuj według otrzymasz oczekiwany wynik.
Jednak masz większą kontrolę nad operacją grupowania rozmytego, rozwijając opcje grupy Rozmyte.
Dostępne są następujące opcje grupowania rozmyte:
W tym przykładzie tabela przekształceń służy do zademonstrowania sposobu mapowania wartości. Tabela przekształceń ma dwie kolumny:
Na poniższej ilustracji przedstawiono tabelę przekształcania użytą w tym przykładzie.
Ważne
Ważne jest, aby tabela przekształceń miała takie same kolumny i nazwy kolumn, jak pokazano na poprzedniej ilustracji (muszą być oznaczone etykietą "Od" i "Do"). W przeciwnym razie dodatek Power Query nie rozpoznaje tabeli jako tabeli przekształceń.
Wróć do okna dialogowego Grupuj według, rozwiń opcje grupy Rozmyte, zmień operację z Liczba wierszy na Wszystkie wiersze, włącz opcję Pokaż wyniki podobieństwa, a następnie wybierz menu rozwijane Tabela przekształceń.
Po wybraniu tabeli przekształceń wybierz przycisk OK. Wynikiem tej operacji są następujące informacje:
W tym przykładzie włączono opcję Ignoruj wielkość liter, więc wartości w kolumnie Od tabeli Przekształcenia są używane do wyszukiwania ciągu tekstowego bez uwzględniania wielkości liter ciągu. Ta operacja przekształcania następuje najpierw, a następnie wykonywana jest operacja grupowania rozmytego.
Wynik podobieństwa jest również wyświetlany w wartości tabeli obok kolumny osoby, która odzwierciedla dokładnie sposób grupowania wartości i ich odpowiednich wyników podobieństwa. Możesz rozwinąć tę kolumnę w razie potrzeby lub użyć wartości z nowych kolumn Frequency dla innych rodzajów przekształceń.
Uwaga
Podczas grupowania według wielu kolumn tabela przekształceń wykonuje operację zamieniania we wszystkich kolumnach, jeśli zamiana wartości zwiększa wynik podobieństwa.
Aby uzyskać więcej informacji na temat sposobu działania tabel przekształceń, przejdź do sekcji Wymagania dotyczące tabeli przekształcania.
Szkolenie
Moduł
Używanie wbudowanych funkcji i funkcji GROUP BY w języku Transact-SQL - Training
Używanie wbudowanych funkcji i funkcji GROUP BY w języku Transact-SQL