Forum Mjukvara Programmering och digitalt skapande Tråd

SQLite - returnera första raden för varje grupp

1
Skriv svar
Permalänk
Medlem ★
●

SQLite - returnera första raden för varje grupp

Jag har en tabell "files" med kolumnerna "directory, name, hash". Jag vill returnera en rad för varje unik hash, och det ska vara den raden som har lägst (directory, name).

Den här frågan returnerar t ex en av raderna för varje hash, men jag tror det är odefinierat vilken av raderna:

SELECT directory, name, hash FROM files GROUP BY hash

I Python skulle det bli något liknande:

def get_first_file_for_each_hash(files)
  """files innehåller en lista i stil med [(directory, name, hash)]"""
  files = sorted(files)
  found_hashes = set()
  for directory, name, hash in files:
    if hash not in found_hashes:
      found_hashes.add(hash)
      yield (directory, name, hash)

ChatGPT gick bet på den här frågan så nu står hoppet till Sweclockers

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

Jag har hittat lösningen själv. Man använder något som heter "window functions", vilket är något jag inte kände till sedan innan. De funkar lite som GROUP BY, men istället för att "slå ihop" rader med identiska kolumner kan man skapa en ny kolumn för varje rad med ett värde som har med gruppen att göra.

Jag skymtade detta i Stackoverflow-svaren när jag sökte på frågan innan jag startade den här tråden, men fick för mig att det var något specifikt för SQLite och en "hackig lösning" och jag ville hellre ha en mer generell lösning, men window functions är tydligen standard i SQL även om det som vanligt är lite småskillnader mellan olika implementationer.

Här är två videos som går igenom det hela som jag tyckte var hyfsat bra, den första är mer teoretisk och den andra mer praktisk med exempel:
https://www.youtube.com/watch?v=e-EL-6Vnkbg
https://www.youtube.com/watch?v=Ww71knvhQ-s

Så svaret på hur man gör är ungefär detta (har inte testkört eftersom min frågeställning var en förenkling och min verkliga tabell och select är lite mer komplicerad):

SELECT 
  directory, 
  name, 
  hash
FROM (
  SELECT 
    directory, 
    name, 
    hash, 
    ROW_NUMBER() OVER (PARTITION BY hash ORDER BY directory, file_name) AS rn
  FROM files)
WHERE rn = 1
ORDER BY directory, file_name

Den inre SELECT:en returnerar alla rader, med kolumnen "rn" tillagd där rader i varje grupp (dvs rader med identisk hash) får ett värde på rn beroende på vilken alfabetisk ordning de kommer i inom gruppen, dvs raden med lägst (directory,name) får värde 1, nästa får värde 2 osv. Den yttre selecten kastar helt enkelt alla rader där rn inte är 1, och sorterar resultatet på directory, file_name.

Redigera
Citera flera Citera
1
Skriv svar