BigQueryのARRAY_AGG関数でソートが効かなくなる問題に直面した

## 概要

BigQueryを利用して、集計をしている際に奇妙な現象に遭遇した。
よくよく調査してみると自分の書いたSQLに問題があるというよりBigQuery自体に問題があるのでは？という結論に至ったので落書き程度に書いておく。

## 問題となったSQL

```SQL
WITH table AS (
  SELECT
    *
  FROM 
    UNNEST (
      [
        STRUCT(1 AS user, 1 AS group_id, '2018-07-17' AS dat),
        (2,1,'2018-07-17'),
        (3,1,'2018-07-17'),
        (4,1,'2018-07-17'),
        (5,1,'2018-07-17'),
        (6,1,'2018-07-17'),
        (7,1,'2018-07-17'),
        (8,2,'2018-07-17'),
        (9,2,'2018-07-17'),
        (10,2,'2018-07-17'),
        (11,2,'2018-07-17'),
        (14,3,'2018-07-17'),
        (15,3,'2018-07-17'),
        (16,3,'2018-07-17'),
        (17,3,'2018-07-17'),
        (1,1,'2018-07-18'),
        (4,1,'2018-07-18'),
        (5,1,'2018-07-18'),
        (6,1,'2018-07-18'),
        (7,1,'2018-07-18'),
        (8,2,'2018-07-18'),
        (9,2,'2018-07-18'),
        (10,2,'2018-07-18'),
        (11,2,'2018-07-18'),
        (12,2,'2018-07-18'),
        (13,2,'2018-07-18'),
        (14,3,'2018-07-18'),
        (15,3,'2018-07-18'),
        (16,3,'2018-07-18'),
        (17,3,'2018-07-18'),
        (18,3,'2018-07-18')
      ]
    )
)

SELECT
  ARRAY_AGG(group_count ORDER BY user_count DESC) AS arr,
  dat
FROM (
  SELECT
    dat, 
    user_count,
    COUNT(*) AS group_count
  FROM (
    SELECT
      dat,
      group_id,
      COUNT(*) AS user_count
    FROM 
      table
    GROUP BY dat, group_id
  )
  GROUP BY dat, user_count 
)
GROUP BY dat
ORDER BY dat ASC
```

## SQLの解説

1つずつ解説。
`WITH句` は単純にサンプルデータを作ってるに過ぎないのでスルー推奨。

```SQL
    SELECT
      dat,
      group_id,
      COUNT(*) AS user_count
    FROM 
      table
    GROUP BY dat, group_id
```

ここはWITH句で作った `table` に対してdat毎、group_id毎に何人のユーザが居るかを集計している。
結果はこんな感じ。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/270795/3c7232e4-5f3d-09f6-de0a-295cb82fcf21.png)


更に広げて

```SQL
  SELECT
    dat, 
    user_count,
    COUNT(*) AS group_count
  FROM (
    SELECT
      dat,
      group_id,
      COUNT(*) AS user_count
    FROM 
      table
    GROUP BY dat, group_id
  )
  GROUP BY dat, user_count 
```

このSQLは先に求めた値に対して更にユーザの数でグルーピングして同じユーザの数のgroup_idの個数を求めている。

結果はこんな感じ。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/270795/1d963b77-fc4c-24ad-162a-79bf94f6e681.png)



そして最後に上記の結果を `ARRAY_AGG関数` を用いてdat毎にグルーピングして `group_count` を配列型に格納する。

```SQL
ARRAY_AGG(group_count ORDER BY user_count DESC) AS arr
```

その際の並び順は `ORDER BY user_count DESC` つまり `user_count` の降順にしている。
つまり結果は下記のようになるはず。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/270795/9796428b-8337-f3fd-5992-26e38e111005.png)

だが実際に実行してみると下記のように `ORDER句` が効いていない結果が帰ってくる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/270795/743549a8-05c0-18eb-4f5c-d1c2602b3518.png)

## 解決方法

結論だけ言うと `COUNT(*)` を2順に実行してる部分が問題になっている模様。
`group_count` `user_count` を取得している箇所で明示的にカラム名を指定することで想定どおりの結果が得られる。(どちらか片方で良い)
例えば下記

```SQL
SELECT
  ARRAY_AGG(group_count ORDER BY user_count DESC) AS arr,
  dat
FROM (
  SELECT
    dat, 
    user_count,
    COUNT(user_count) AS group_count
  FROM (
    SELECT
      dat,
      group_id,
      COUNT(*) AS user_count
    FROM 
      table
    GROUP BY dat, group_id
  )
  GROUP BY dat, user_count 
)
GROUP BY dat
ORDER BY dat ASC
```

## 結論

`COUNT(*)` で無精せずにカラム名を明示的に書けば良い。
だが、別にNULL値を含んでるわけではないので `COUNT(カラム名)` と `COUNT(*)` の間に差異があるのは解せない問題である。
書き方に問題があるというよりBigQuery側に問題があると思いたい。

そもそも、こんな特殊なSQLを書く機会はあまりないとは思うが、BigQueryで `ARRAY_AGG関数` を利用する場合は注意しましょう。
という落書きでした。

## 補足

#### Postgresの場合

少しSQLを改変してPostgresqlに同等のSQLを投げてみると想定どおりの状態になる。

```
  arr  |    dat
-------+------------
 {1,2} | 2018-07-17
 {1,2} | 2018-07-18
```

## 追伸

なぜこういう挙動を取るかわかる方がいらっしゃいましたら教えて頂ければです。