JSON to Insights：快速而簡單

有很多JSON資料集，然後是JSON資料集。 每個公司，包括您的公司，都在JSON中儲存了大量資料 - 調查，活動和論壇的結果。

有許多方法可以讓皮膚成為JSON。 您可以為每個報告編寫Python程式，您可以進行視覺化。 或者，您可以使用（SQL for JSON） 生成正確的演算法   來分析JSON資料。 在本文中，我們將向您展示如何使用N1QL快速提取洞察力。 我們還使用了下一版本中的兩個功能：公用表表示式（CTE）和視窗函式。

目標：將公共JSON資料集用於美國公開賽高爾夫比分，以建立簡單的排行榜，排名等。

你要做的三件事：

1. 輕鬆將資料提取到Couchbase中。

2. 立即開始獲取此JSON資料的值。

3. 塑造JSON以快速使用新功能生成有用的報告。

來源資料： https ： //github.com/jackschultz/usopen

此帖子中的查詢也可在以下 網址 獲得： https ： //github.com/keshavmr/usopen-golf-queries

資料 倉庫 結構： 此GitHub  倉庫 包含US Open golf -2018資料。 對於每個洞，它作為每天的單獨檔案。

每個文件都有這種結構。 這是第1天第1洞的檔案。提交的Ps有玩家列表，每個玩家都有一個唯一的ID。

每個玩家的遊戲統計資料都是按行程劃分的。 使用玩家的場唯一ID將玩家與得分相匹配。

開始獲取見解：

在開始查詢之前，請在儲存桶上建立主索引。

在usopen上建立主要指數;

任務1： 按輪次和最終總數建立球員得分報告。

在自下而上使用JSON後，我們想出了這個查詢。 解釋是在查詢之後。

表格結果（以表格形式，來自Couchbase查詢工作臺）

與AS（

  選擇

    pl .hnum  AS holedn，

    pl .ps .Nat  AS country，

    （pl .ps .FN || “” || pl .ps .LN）AS名稱，

    pl .ps .ID  AS ID，

    array_length（hps .Sks）AS得分，

    HPL .hole  AS `hole`，

    HPL 。天 AS `day`

  從

    （

      選擇

        meta（usopen）.id  AS hnum，

        PS

      從

        usopen USE鍵“孔：1：1”不需要Ps AS ps

    ）pl

    INNER JOIN（

      選擇

        TONUMBER（分裂（間（usopen）.ID，“：”）[ 1 ]）AS `hole`，

        TONUMBER（分裂（間（usopen）.ID，“：”）[ 2 ]）AS `day`，

        HPS

      從

        usopen不需要Rs AS rs UNNEST rs .Hs  AS hs UNNEST hs.HPs  AS hps

    ）hpl ON（pl .ps .ID = hps .ID）

）

選擇

  d .name，

  和（

    例如d .day = 1那麼d。得分 ELSE 0 END

  ）R1，

  和（

    例如d .day = 2那麼d。得分 ELSE 0 END

  ）R2，

  和（

    例如d .day = 3那麼d。分數 ELSE 0 END

  ）R3，

  和（

    例如，d .day = 4那麼d。分數 ELSE 0 END

  ）R4，

  SUM（d .score）T

從

  d

GROUP  BY

  d .name

ORDER  BY

  d .name

讓我們逐塊檢視查詢。

看看WITH d子句。 該宣告將JSON從PER-day-PER-hole-by-shot-data資料解碼為簡單的標量值。

• Holedn是文件金鑰 - 空洞日數

• 國家是球員的國籍

• ID是玩家的唯一ID。

洞和日是顯而易見的，得分是該洞的球員得分。

在SELECT語句的FROM子句中，pl是從第一天的第一個洞（孔：1：1）中取出的文件的完整列表。

Rs是球員的結果，一杆一杆地射門。 首先，我們不需要那個陣列來計算每個洞的細節和那個洞的得分，由array_length（hps.Sks）決定。

一旦我們獲得了逐洞分數，就可以很容易地將最終查詢寫入由玩家和白天進行聚合。

** WITH子句是即將釋出的Mad-Hatter版本中的公用表表示式（CTE）功能。 在Couchbase 5.5或更低版本中執行此操作的舊方法是使用LET子句。 如果您需要幫助，請在Couchbase論壇中釋出問題）。

任務2：現在，建立完整的排行榜並新增資訊。 被削減的高爾夫球手不會參加第三輪或第四輪比賽。 我們使用這些資訊來確定被裁掉的球員。

查詢2. 獲取上一個查詢並將其命名為公用表dx，然後新增以下表示式以確定該剪下。

這是完整的查詢：

與dy AS（

  選擇

    pl .hnum  AS holedn，

    pl .ps .Nat  AS country，（pl .ps .FN || “” || pl .ps .LN）AS名稱，

    pl .ps .ID  AS ID，

    array_length（hps .Sks）AS得分，

    HPL .hole  AS `hole`，

    HPL 。天 AS `day`

  從

    （

      選擇

        meta（usopen）.id  AS hnum，

        PS

      從

        usopen USE鍵“孔：1：1”不需要Ps AS ps

    ）pl

    INNER JOIN（

      選擇

        TONUMBER（分裂（間（usopen）.ID，“：”）[ 1 ]）AS `hole`，

        TONUMBER（分裂（間（usopen）.ID，“：”）[ 2 ]）AS `day`，

        HPS

      從

        usopen不需要Rs AS rs 不需要.Hs  AS hs 不需要hs.HPs  AS hps

    ）hpl ON（pl .ps .ID = hps .ID）

）

dx AS（

  選擇

    d .name，

    和（

      例如d .day = 1那麼d。得分 ELSE 0 END

    ）R1，

    和（

      例如d .day = 2那麼d。得分 ELSE 0 END

    ）R2，

    和（

      例如d .day = 3那麼d。分數 ELSE 0 END

    ）R3，

    和（

      例如，d .day = 4那麼d。分數 ELSE 0 END

    ）R4，

    sum（d .score）T

  從

    dy AS d

  GROUP  BY

    d .name

  ORDER  BY

    d .name

）

選擇

  d2 .name，

  d2 .R 1，

  d2 .R 2，

  d2 .R 3，

  d2 .R 4，

  d2 .T，（

    情況何時（

      d2 .R 1 = 0

      或者 d2 .R 2 = 0

      或者 d2 .R 3 = 0

      或者 d2 .R 4 = 0

    ）那麼“削減”就會失敗

  ）AS CUT

從

  dx AS d2

ORDER  BY

  削減ASC，

  d2 .T  ASC

任務3：確定獲勝者。

我們需要根據總得分對玩家進行排名，以確定誰贏得了比賽。 如果分數中存在關聯，則跳過排名。 在 沒有視窗函式的  SQL中執行此操作 非常昂貴 。 在這裡，我們使用RANK（）視窗函式編寫查詢。 視窗函式是即將釋出的版本（Mad-Hatter）中N1QL的一項功能

注意因為平局得分而排名4,8,9,10,1缺失！

任務4：現在，讓我們看看每個球員在第一輪，第二輪，第三輪比賽後的表現與最後一輪比較。 使用視窗功能，使巧克力覆蓋的棉花糖消失變得容易。

查詢4： 使用相同的RANK（）函式，按ORDER BY每天的分數（第1天，第1天+第2天，第1天+第2天+第3天）而不是最終得分。

與dy AS（

  選擇

    pl .hnum  AS holedn，

    pl .ps .Nat  AS country，（pl .ps .FN || “” || pl .ps .LN）AS名稱，

    pl .ps .ID  AS ID，

    array_length（hps .Sks）AS得分，

    HPL .hole  AS `hole`，

    HPL 。天 AS `day`

  從

    （

      選擇

        meta（usopen）.id  AS hnum，

        PS

      從

        usopen USE鍵“孔：1：1”不需要Ps AS ps

    ）pl

    INNER JOIN（

      選擇

        TONUMBER（分裂（間（usopen）.ID，“：”）[ 1 ]）AS `hole`，

        TONUMBER（分裂（間（usopen）.ID，“：”）[ 2 ]）AS `day`，

        HPS

      從

        usopen不需要Rs AS rs 不需要.Hs  AS hs 不需要hs.HPs  AS hps

    ）hpl ON（pl .ps .ID = hps .ID）

）

dx AS（

  選擇

    d .name，

    和（

      例如d .day = 1那麼d。得分 ELSE 0 END

    ）R1，

    和（

      例如d .day = 2那麼d。得分 ELSE 0 END

    ）R2，

    和（

      例如d .day = 3那麼d。分數 ELSE 0 END

    ）R3，

    和（

      例如，d .day = 4那麼d。分數 ELSE 0 END

    ）R4，

    sum（d .score）T

  從

    dy AS d

  GROUP  BY

    d .name

  ORDER  BY

    d .name

）

選擇

  d2 .name，

  d2 .R 1，

  d2 .R 2，

  d2 .R 3，

  d2 .R 4，

  d2 .T，

  DENSE_RANK（）超過（

    ORDER  BY

      d2 .T + CUT

  ）AS rankMoney，

  RANK（）超過（

    ORDER  BY

      d2 .T + CUT

  ）AS rankFinal，

  RANK（）超過（

    ORDER  BY

      d2 .R 1

  ）AS round1rank，

  RANK（）超過（

    ORDER  BY

      d2 .R 1 + d2 .R 2

  ）AS round2rank，

  RANK（）超過（

    ORDER  BY

      d2 .R 1 + d2 .R 2 + d2 .R 3 + CUT

  ）AS round3rank

從

  dx AS d2 LET CUT =（

    情況何時（

      d2 .R 1 = 0

      或者 d2 .R 2 = 0

      或者 d2 .R 3 = 0

      或者 d2 .R 4 = 0

    ）然後1000 ELSE 0 END

  ）

ORDER  BY

  rankFinal，

  round1rank，

  round2rank，

  round3rank

現在你可以看到球員每天如何上下移動。

任務5：使用基本的逐個鏡頭統計資料為領導者建立完整記分卡。

查詢5：  Brooks Koepka是美國公開賽的最終贏家。 讓我們逐洞獲得他的分數，然後逐輪獲得他的累積分數。 注意簡單的SUM（）和COUNT（）聚合如何作為具有OVER（）子句的視窗函式。

這首先按天劃分分數，然後按洞的順序劃分 - 由PARTITION BY子句指定，按洞的順序，1-18。 然後，SUM將得分加起來。

這個SUM（）函式只是將第1天，第1洞到第4天，第18洞的得分加起來 - 這是由OVER（）子句中的ORDER BY d3.day，d3.hole指定的。 ToTScore領域顯示Koepka在每個洞的錦標賽總短缺。

與dy AS（

  選擇

    pl .hnum  AS holedn，

    pl .ps .Nat  AS country，（pl .ps .FN || “” || pl .ps .LN）AS名稱，

    pl .ps .ID  AS ID，

    array_length（hps .Sks）AS得分，

    HPL .hole  AS `hole`，

    HPL 。天 AS `day`，

    hpl .Par  AS Par

  從

    （

      選擇

        meta（usopen）.id  AS hnum，

        PS

      從

        usopen USE鍵“孔：1：1”不需要Ps AS ps

      哪裡

        ps .LN = “Koepka”

    ）pl

    INNER JOIN（

      選擇

        TONUMBER（分裂（間（usopen）.ID，“：”）[ 1 ]）AS `hole`，

        TONUMBER（分裂（間（usopen）.ID，“：”）[ 2 ]）AS `day`，

        hs .Par，

        HPS

      從

        usopen不需要Rs AS rs 不需要.Hs  AS hs 不需要hs.HPs  AS hps

    ）hpl ON（pl .ps .ID = hps .ID）

）

dx AS（

  選擇

    d .name，

    d .day，

    d。分數，

    d .hole，

    d .Par

  從

    dy AS d

  ORDER  BY

    d .name

）

dz AS（

  選擇

    d2 .day，

    d2 .hole，

    d2 .score，

    SUM（d2 .score）OVER（

      PARTITION BY D2 ·天

      ORDER  BY

        d2 .hole

    ）hst，

    d2 .Par，

    SUM（d2 .Par）OVER（

      PARTITION BY D2 ·天

      ORDER  BY

        d2 .hole

    ）hpr

  從

    dx AS d2 LET CUT =（

      情況何時（

        d2 .R 1 = 0

        或者 d2 .R 2 = 0

        或者 d2 .R 3 = 0

        或者 d2 .R 4 = 0

      ）然後1000 ELSE 0 END

    ）

  ORDER  BY

    d2 .day，

    d2 .hole

）

選擇

  d3 .Par，

  d3 .day，

  d3 .hole，

  d3 .hst，

  d3 .score，（d3 .hst -  d3 .hpr）ToPar，

  總和（d3 .score）OVER（

    ORDER  BY

      d3 .day，

      d3 .hole

  ）ToTScore，

  數（1）OVER（

    ORDER  BY

      d3 .day，

      d3 .hole

  ）HoleNum

從

  dz AS d3