> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Peut-on faire un PIVOT dans ClickHouse ?

> ClickHouse n’a pas de clause PIVOT, mais nous pouvons nous en approcher en utilisant des combinateurs de fonctions d’agrégation. Voyons comment procéder avec le jeu de données des prix de l’immobilier au Royaume-Uni.

<div id="introduction">
  ## Introduction
</div>

ClickHouse n'a pas d'opérateur pivot, mais nous pouvons obtenir un comportement similaire à l'aide des [combinateurs de fonctions d'agrégation](/fr/reference/functions/aggregate-functions/combinators) et, en particulier, de ceux dotés du [suffixe `-Map`](/fr/reference/functions/aggregate-functions/combinators#-map).

Dans cet article, nous allons voir comment faire.
Une vidéo couvrant le même sujet est également disponible, que vous pouvez regarder ci-dessous :

<Frame>
  <iframe src="https://www.youtube.com/embed/nlRMOmwYtF4?si=0TZSNg-uo7zjiO52" title="Lecteur vidéo YouTube" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen />
</Frame>

<div id="understanding-aggregate-function-combinators">
  ## Comprendre les combinateurs de fonctions d’agrégation
</div>

Commençons par un exemple simple. Nous allons utiliser [clickhouse-local](/fr/concepts/features/tools-and-utilities/clickhouse-local), que vous pouvez démarrer en exécutant la commande suivante :

```bash theme={null}
clickhouse -m --output_format_pretty_row_numbers=0
```

La requête suivante appelle la fonction `sumMap`, qui prend en entrée une map et additionne les valeurs de chaque clé :

```sql theme={null}
SELECT sumMap(map('ClickHouse', 1, 'ClickBench', 2));
```

```text theme={null}
┌─sumMap(map('ClickHouse', 1, 'ClickBench', 2))─┐
│ {'ClickBench':2,'ClickHouse':1}               │
└───────────────────────────────────────────────┘
```

Ce n'est pas un exemple particulièrement intéressant, puisqu'il renvoie la même Map que celle passée en entrée.
Appelons maintenant `sumMap` sur plusieurs lignes de Maps ;

```sql theme={null}
WITH values AS (
  SELECT map('ClickHouse', 3) AS value
  UNION ALL
  SELECT map('ClickBench', 2, 'ClickHouse', 4) AS value
)
SELECT sumMap(value)
FROM values;
```

```text theme={null}
┌─sumMap(value)───────────────────┐
│ {'ClickBench':2,'ClickHouse':7} │
└─────────────────────────────────┘
```

La clé `ClickHouse` apparaissait sur les deux lignes et ses valeurs ont été additionnées. La clé `ClickBench` n’était présente que sur une seule ligne ; une seule valeur est donc additionnée, ce qui renvoie cette valeur !

Nous pouvons également utiliser `maxMap` pour trouver les valeurs maximales pour chaque clé :

```sql theme={null}
WITH values AS (
  SELECT map('ClickHouse', 3) AS value
  UNION ALL
  SELECT map('ClickBench', 2, 'ClickHouse', 4) AS value
)
SELECT maxMap(value)
FROM values;
```

```text theme={null}
┌─maxMap(value)───────────────────┐
│ {'ClickBench':2,'ClickHouse':4} │
└─────────────────────────────────┘
```

Ou nous pouvons également utiliser `avgMap` pour trouver la valeur moyenne pour chaque clé :

```sql theme={null}
WITH values AS (
  SELECT map('ClickHouse', 3) AS value
  UNION ALL
  SELECT map('ClickBench', 2, 'ClickHouse', 4) AS value
)
SELECT avgMap(value)
FROM values;
```

```text theme={null}
┌─avgMap(value)─────────────────────┐
│ {'ClickBench':2,'ClickHouse':3.5} │
└───────────────────────────────────┘
```

Nous espérons que cela vous a permis de mieux comprendre le fonctionnement de ces combinateurs de fonctions.

<div id="real-world-application-uk-housing-prices-dataset">
  ## Application concrète : jeu de données sur les prix de l’immobilier au Royaume-Uni
</div>

Nous allons maintenant les utiliser sur un jeu de données plus volumineux dans le [ClickHouse SQL playground](https://clickhouse.com/blog/announcing-the-new-sql-playground).

Nous pouvons nous connecter au playground à l’aide de clickhouse-client :

```bash theme={null}
clickhouse client -m \
  -h sql-clickhouse.clickhouse.com \
  -u demo \
  --secure
```

Nous allons interroger la table `uk_price_paid` ; examinons donc les données qu’elle contient :

```sql theme={null}
SELECT * FROM uk.uk_price_paid LIMIT 1 FORMAT Vertical;
```

```text theme={null}
Row 1:
──────
price:     145000
date:      2008-11-19
postcode1:
postcode2:
type:      semi-detached
is_new:    0
duration:  leasehold
addr1:
addr2:
street:    CURLEW DRIVE
locality:  SCARBOROUGH
town:      SCARBOROUGH
district:  SCARBOROUGH
county:    NORTH YORKSHIRE
category:  0
```

Comme on peut le voir ci-dessus, la table contient différents champs liés aux ventes immobilières au Royaume-Uni.

<div id="grouping-and-aggregating-by-decade">
  ### Regroupement et agrégation par décennie
</div>

Calculons, pour chaque décennie du jeu de données, les prix médians par comté :

```sql theme={null}
WITH year(toStartOfInterval(date, toIntervalYear(10))) AS year
SELECT
    county,
    medianMap(map(year, price)) AS medianPrices
FROM uk.uk_price_paid
GROUP BY ALL
ORDER BY max(price) DESC
LIMIT 10;
```

```text theme={null}
    ┌─county─────────────┬─medianPrices───────────────────────────────────────┐
 1. │ GREATER LONDON     │ {1990:89972.5,2000:215000,2010:381500,2020:485000} │
 2. │ TYNE AND WEAR      │ {1990:46500,2000:93000,2010:130000,2020:139000}    │
 3. │ WEST MIDLANDS      │ {1990:50000,2000:110000,2010:149950,2020:185000}   │
 4. │ GREATER MANCHESTER │ {1990:47000,2000:97000,2010:141171,2020:178000}    │
 5. │ MERSEYSIDE         │ {1990:46750,2000:94972.5,2010:128000,2020:149000}  │
 6. │ HERTFORDSHIRE      │ {1990:86500,2000:193000,2010:315000,2020:415000}   │
 7. │ WEST YORKSHIRE     │ {1990:48995,2000:99950,2010:139000,2020:164950}    │
 8. │ BRIGHTON AND HOVE  │ {1990:70000,2000:173000,2010:288000,2020:387000}   │
 9. │ DORSET             │ {1990:76500,2000:182000,2010:250000,2020:315000}   │
10. │ HAMPSHIRE          │ {1990:79950,2000:177500,2010:260000,2020:335000}   │
    └────────────────────┴────────────────────────────────────────────────────┘
```

<div id="filtering-results">
  ### Filtrer les résultats
</div>

Nous pouvons filtrer les résultats pour n’inclure que les données à partir de 2010 :

```sql theme={null}
WITH year(toStartOfInterval(date, toIntervalYear(10))) AS year
SELECT
    county,
    medianMap(map(year, price)) AS medianPrices
FROM uk.uk_price_paid
WHERE year >= 2010
GROUP BY ALL
ORDER BY max(price) DESC
LIMIT 10;
```

```text theme={null}
    ┌─county─────────────┬─medianPrices────────────────┐
 1. │ GREATER LONDON     │ {2010:384975,2020:485919.5} │
 2. │ TYNE AND WEAR      │ {2010:130000,2020:140000}   │
 3. │ WEST MIDLANDS      │ {2010:146500,2020:185000}   │
 4. │ GREATER MANCHESTER │ {2010:140000,2020:177500}   │
 5. │ MERSEYSIDE         │ {2010:130000,2020:150000}   │
 6. │ HERTFORDSHIRE      │ {2010:315000,2020:415000}   │
 7. │ WEST YORKSHIRE     │ {2010:140000,2020:162500}   │
 8. │ BRIGHTON AND HOVE  │ {2010:287500,2020:387000}   │
 9. │ DORSET             │ {2010:255750,2020:315000}   │
10. │ HAMPSHIRE          │ {2010:265000,2020:330000}   │
    └────────────────────┴─────────────────────────────┘
```

<div id="combining-multiple-aggregations">
  ## Combiner plusieurs agrégations
</div>

Si nous voulons trouver le prix maximal par décennie, nous pouvons le faire à l’aide de la fonction `maxMap` que nous avons vue plus haut :

```sql theme={null}
WITH year(toStartOfInterval(date, toIntervalYear(10))) AS year
SELECT
    county,
    medianMap(map(year, price)) AS medianPrices,
    maxMap(map(year, price)) AS maxPrices
FROM uk.uk_price_paid
WHERE year >= 2010
GROUP BY ALL
ORDER BY max(price) DESC
LIMIT 10;
```

```text theme={null}
    ┌─county─────────────┬─medianPrices──────────────┬─maxPrices───────────────────────┐
 1. │ GREATER LONDON     │ {2010:385000,2020:485250} │ {2010:594300000,2020:630000000} │
 2. │ TYNE AND WEAR      │ {2010:130000,2020:141000} │ {2010:448300979,2020:93395000}  │
 3. │ WEST MIDLANDS      │ {2010:149000,2020:184250} │ {2010:415000000,2020:104500000} │
 4. │ GREATER MANCHESTER │ {2010:140000,2020:175000} │ {2010:107086856,2020:319186000} │
 5. │ MERSEYSIDE         │ {2010:129950,2020:150000} │ {2010:300000000,2020:93395000}  │
 6. │ HERTFORDSHIRE      │ {2010:315000,2020:415000} │ {2010:254325163,2020:93395000}  │
 7. │ WEST YORKSHIRE     │ {2010:138500,2020:165000} │ {2010:246300000,2020:109686257} │
 8. │ BRIGHTON AND HOVE  │ {2010:285000,2020:387000} │ {2010:200000000,2020:71540000}  │
 9. │ DORSET             │ {2010:250000,2020:315000} │ {2010:150000000,2020:20230000}  │
10. │ HAMPSHIRE          │ {2010:264000,2020:330000} │ {2010:150000000,2020:48482500}  │
    └────────────────────┴───────────────────────────┴─────────────────────────────────┘
```

<div id="applying-functions-to-map-values">
  ## Appliquer des fonctions aux valeurs d'une map
</div>

Nous pouvons également calculer le prix moyen à l'aide de `avgMap`.
Ces valeurs comportent beaucoup de décimales, que nous pouvons simplifier en utilisant la fonction [`mapApply`](/fr/reference/functions/regular-functions/tuple-map-functions#mapApply) pour appeler la fonction [`floor`](/fr/reference/functions/regular-functions/rounding-functions#floor) sur chaque valeur de la map :

```sql theme={null}
WITH year(toStartOfInterval(date, toIntervalYear(10))) AS year
SELECT
    county,
    medianMap(map(year, price)) AS medianPrices,
    mapApply((k, v) -> (k, floor(v)), avgMap(map(year, price))) AS avgPrices
FROM uk.uk_price_paid
WHERE year >= 2010
GROUP BY ALL
ORDER BY max(price) DESC
LIMIT 10;
```

```text theme={null}
    ┌─county─────────────┬─medianPrices──────────────┬─avgPrices─────────────────┐
 1. │ GREATER LONDON     │ {2010:382000,2020:490000} │ {2010:626091,2020:807240} │
 2. │ TYNE AND WEAR      │ {2010:127000,2020:140000} │ {2010:176955,2020:225770} │
 3. │ WEST MIDLANDS      │ {2010:148500,2020:183000} │ {2010:204128,2020:257226} │
 4. │ GREATER MANCHESTER │ {2010:140000,2020:177500} │ {2010:195592,2020:251165} │
 5. │ MERSEYSIDE         │ {2010:127995,2020:150000} │ {2010:182194,2020:206062} │
 6. │ HERTFORDSHIRE      │ {2010:317500,2020:415000} │ {2010:414134,2020:529409} │
 7. │ WEST YORKSHIRE     │ {2010:140000,2020:164500} │ {2010:185121,2020:234870} │
 8. │ BRIGHTON AND HOVE  │ {2010:285000,2020:387000} │ {2010:372285,2020:527184} │
 9. │ DORSET             │ {2010:250000,2020:315000} │ {2010:305581,2020:370739} │
10. │ HAMPSHIRE          │ {2010:265000,2020:330000} │ {2010:335945,2020:425196} │
    └────────────────────┴───────────────────────────┴───────────────────────────┘
```

<div id="flexible-grouping-counties-districts-and-postcodes">
  ## Regroupement flexible : comtés, districts et codes postaux
</div>

Essayons de regrouper par différents champs.
Cette fois, nous allons calculer le prix médian par décennie, regroupé par comté et par district :

```sql theme={null}
WITH year(toStartOfInterval(date, toIntervalYear(10))) AS year
SELECT
    county,
    district,
    medianMap(map(year, price)) AS medianPrices
FROM uk.uk_price_paid
WHERE year >= 2010
GROUP BY ALL
ORDER BY max(price) DESC
LIMIT 10
```

```text theme={null}
    ┌─county─────────────┬─district───────────────┬─medianPrices────────────────┐
 1. │ GREATER LONDON     │ CROYDON                │ {2010:298475,2020:400000}   │
 2. │ GREATER LONDON     │ CITY OF WESTMINSTER    │ {2010:800000,2020:935000}   │
 3. │ GREATER LONDON     │ SOUTHWARK              │ {2010:437000,2020:540000}   │
 4. │ TYNE AND WEAR      │ NEWCASTLE UPON TYNE    │ {2010:144000,2020:162500}   │
 5. │ WEST MIDLANDS      │ WALSALL                │ {2010:137450,2020:162000}   │
 6. │ GREATER LONDON     │ CITY OF LONDON         │ {2010:725875,2020:840000}   │
 7. │ GREATER LONDON     │ HILLINGDON             │ {2010:329125,2020:439000}   │
 8. │ GREATER MANCHESTER │ MANCHESTER             │ {2010:144972.5,2020:190000} │
 9. │ GREATER LONDON     │ HAMMERSMITH AND FULHAM │ {2010:622250,2020:750000}   │
10. │ GREATER LONDON     │ ISLINGTON              │ {2010:500000,2020:640000}   │
    └────────────────────┴────────────────────────┴─────────────────────────────┘
```

Nous pourrions également choisir de regrouper par année, puis de concaténer `postcode1` et `postcode2` dans la map :

```sql theme={null}
WITH year(toStartOfInterval(date, toIntervalYear(10))) AS year
SELECT
    year,
    medianMap(map(postcode1 || ' ' || postcode2, price)) AS medianPrices
FROM uk.uk_price_paid
WHERE postcode1 LIKE 'NP1'
GROUP BY ALL;
```

```text theme={null}
   ┌─year─┬─medianPrices────────────────────────────────────────────────────────┐
1. │ 1990 │ {'NP1 4PB':9000}                                                    │
2. │ 2000 │ {'NP1 4SR':28475,'NP1 7HZ':200000}                                  │
3. │ 2010 │ {'NP1 4PB':5000,'NP1 4QJ':1075000,'NP1 4SR':58000,'NP1 8BR':200000} │
4. │ 2020 │ {'NP1 5DW':140000}                                                  │
   └──────┴─────────────────────────────────────────────────────────────────────┘
```