Ni nini window functions?

Question

Accepted Answer

**Window functions** hufanya hesabu katika seti ya safu ("window") **zinazohusiana na safu ya sasa**, *bila kuziunganisha katika safu moja ya matokeo* (tofauti na aggregates na `GROUP BY`). Huwezesha uchambuzi wenye nguvu — jumla zinazokimbia, vyeo, ulinganisho na safu nyingine — wakati zikiweka kila safu katika matokeo.

## Tofauti muhimu na 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.
```

## Functions za kupanga vyeo (ranking)

```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;
```

Kifungu cha `OVER (...)` hufafanua window. **`PARTITION BY`** hugawanya safu katika makundi (kama GROUP BY, lakini kwa kila safu), na **`ORDER BY`** hupanga ndani ya kila partition. `ROW_NUMBER`/`RANK`/`DENSE_RANK` hutoa vyeo.

## Aggregate window functions (jumla zinazokimbia, aggregates kwa kila kundi)

```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;
```

Aggregates kama window functions huhesabu jumla zinazokimbia, wastani unaosogea, au aggregates kwa kila partition wakati zikiweka kila safu — haiwezekani kwa GROUP BY peke yake.

## LAG/LEAD — kulinganisha na safu nyingine

```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`** hufikia thamani kutoka safu zilizopita/zinazofuata — bora kwa ulinganisho wa kipindi-kwa-kipindi (ukuaji wa mwezi-kwa-mwezi, n.k.).

## Kwa nini ni muhimu

Window functions ni kipengele chenye nguvu, cha hali ya juu cha SQL cha lazima kwa **hoja za uchambuzi**, na kuzielewa ni maarifa ya thamani ya kiwango cha senior kwa uchambuzi wa data, kuripoti, na kuhoji changamano.

Uwezo wao unaowabainisha — kufanya hesabu katika safu zinazohusiana **wakati zikiweka kila safu** (tofauti na GROUP BY, inayounganisha safu) — huwezesha darasa zima la uchambuzi ambao ni mgumu au hauwezekani vinginevyo: **vyeo** (`ROW_NUMBER`/`RANK`/`DENSE_RANK` — kupata N bora kwa kila kundi, kutoa vyeo), **jumla zinazokimbia na wastani unaosogea** (jumla za nyongeza, aggregates kwa partition zinazoonyeshwa pamoja na kila safu), na **ulinganisho wa safu-kwa-safu** (`LAG`/`LEAD` kwa uchambuzi wa kipindi-kwa-kipindi kama ukuaji wa mwezi-kwa-mwezi).

Haya ni mahitaji ya kawaida, ya thamani ya uchambuzi (leaderboards, vipimo vya nyongeza, uchambuzi wa mwelekeo, kulinganisha kila safu na wastani wa kundi lake) ambayo window functions hushughulikia kwa uzuri katika hoja moja, ambapo SQL kabla ya window functions ilihitaji self-joins changamano au subqueries.

Kuelewa syntax ya `OVER (PARTITION BY ... ORDER BY ...)` (kufafanua window), functions za kupanga vyeo, aggregate window functions (jumla zinazokimbia, aggregates kwa partition), na `LAG`/`LEAD` (kufikia safu nyingine) kunafunika seti ya zana yenye nguvu ya uchambuzi.

Kwa kuwa uchambuzi wa data na kuripoti kwa hali ya juu ni matumizi muhimu ya SQL, na kwa kuwa window functions ni njia ya kisasa, yenye nguvu ya kueleza vyeo, hesabu zinazokimbia, na ulinganisho wa safu (yenye uwezo zaidi na uzuri kuliko mbadala), kumudu window functions ni maarifa ya thamani, yanayotumika mara kwa mara kwa SQL ya uchambuzi yanayowatofautisha watengenezaji wanaoweza kuandika hoja za data za hali ya juu — alama ya ujuzi wa hali ya juu wa SQL na mada ya kawaida katika nafasi na mahojiano yanayolenga data.