Window functions su nini?

Question

Accepted Answer

**Window functions** suna yin ƙididdige aiki a cikin saitin layuka ("window") **da suka shafi jajin yanzu**, *ba ta canza su zuwa sakamakon jajin ɗaya* (ba kamar aggregates tare da `GROUP BY`). Suna ba da damar ƙwararru na bincike — jmlace mai gudu, ranji, kwatantawa tare da wasu layuka — yayin da kake ajiye kowane jaji a sakamakon.)

## Bambance-bambancen mahimman daga GROUP BY

```text
GROUP BY      → collapses groups into ONE row each (you lose individual rows)
Window function → computes across related rows but KEEPS every row
→ You get per-row detail AND aggregate/ranking info together.
```

## Ayyukan ranji

```sql
SELECT
  name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,     -- unique sequential rank
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,  -- rank within dept
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
```

Klasal `OVER (...)` yana ayyana window. **`PARTITION BY`** yana raba layuka zuwa ƙungiyoyi (kamar GROUP BY, amma kowane jaji), da **`ORDER BY`** yana umurta a cikin kowane rabo. `ROW_NUMBER`/`RANK`/`DENSE_RANK` suna ba da ranji.

## Ayyukan aggregate window (jmlace mai gudu, aggregate per-group)

```sql
SELECT
  date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,            -- cumulative sum
  AVG(amount) OVER (PARTITION BY category) AS category_avg,      -- avg per category, kept per row
  amount - AVG(amount) OVER (PARTITION BY category) AS diff_from_avg
FROM transactions;
```

Aggregate azaman ayyukan window suna ƙididdige jmlace mai gudu, matsakaicin motsa, ko aggregate per-partition yayin da kake ajiye kowane jaji — ba zai yiwu tare da GROUP BY kaɗai.

## LAG/LEAD — kwatanta zuwa wasu layuka

```sql
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,             -- the previous row's value
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth,        -- month-over-month change
  LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;
```

**`LAG`/`LEAD`** suna shiga ƙimar da aka sani tun layuka na jiya/gida — aiki gida don kwatantawa tsakanin lokaci (haɓaka tsakanin buwan wata, da sauransu).

## Me ya sa yana da mahimmanci

Window functions sune aiki mai ƙarfi, sakamako na SQL ainihi dole ne don **bubugan bincike**, kuma fahimtar su yana da daraja don sanannen manyan halitta na ƙididdige bayanai, rajistar, da ƙididdiga masu rikitarwa.

Aiki na siffantarwa — yin ƙididdige aiki a cikin layuka masu alaƙa **yayin da kake ajiye kowane jaji** (ba kamar GROUP BY, wanda ya raba layuka) — yana buƙatar nau'i gida na bincike da ba zai yiwu sauransu ba: **ranji** (`ROW_NUMBER`/`RANK`/`DENSE_RANK` — nemo N na gida ta ƙungiya, ba ranji), **jmlace mai gudu da matsakaicin motsa** (jmlaice tare, aggregate per-partition wajen kowane jaji), da **kwatantawa layuka-zuwa-layuka** (`LAG`/`LEAD` don bincike tsakanin lokaci kamar haɓaka tsakanin buwan wata).
"`OVER (PARTITION BY ... ORDER BY ...)` Syntax, ayyukan ranji, ayyukan aggregate window, da `LAG`/`LEAD` suna aiki na ƙarfi na bincike.

Tun bincike bayanai da rajistar aiki na mahimmanci na SQL, kuma window functions sune hanyar zamani, ƙarfi don bayyana ranji, ƙididdige raɗi, da kwatantawa layuka (ƙarfi da kyau fi abubuwanda), fahimtar window functions yana da mahimmanci, ilimin SQL na bincike akai-akai - wani safo na SQL na musamman da aiki na gida na bayanai.