<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Pei Lee on Medium]]></title>
        <description><![CDATA[Stories by Pei Lee on Medium]]></description>
        <link>https://medium.com/@peilee-98185?source=rss-6a3cf9d12806------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*aKo3NyIfHRi4EOFw.</url>
            <title>Stories by Pei Lee on Medium</title>
            <link>https://medium.com/@peilee-98185?source=rss-6a3cf9d12806------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 00:28:32 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@peilee-98185/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[電商也能是產品導向式成長的服務嗎]]></title>
            <link>https://peilee-98185.medium.com/%E9%9B%BB%E5%95%86%E4%B9%9F%E8%83%BD%E6%98%AF%E7%94%A2%E5%93%81%E5%B0%8E%E5%90%91%E5%BC%8F%E6%88%90%E9%95%B7%E7%9A%84%E6%9C%8D%E5%8B%99%E5%97%8E-c528c0c9c96a?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/c528c0c9c96a</guid>
            <category><![CDATA[ecommerce]]></category>
            <category><![CDATA[product-led-growth]]></category>
            <category><![CDATA[headless-commerce]]></category>
            <category><![CDATA[shoppable-content]]></category>
            <category><![CDATA[social-commerce]]></category>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Wed, 02 Nov 2022 13:42:24 GMT</pubDate>
            <atom:updated>2022-11-02T13:42:24.624Z</atom:updated>
            <content:encoded><![CDATA[<p>最近在 <a href="https://www.facebook.com/photo/?fbid=1484116208667504&amp;set=a.214707742275030">曼報</a> 看到了「產品導向式成長 ( Product-led Growth, 簡稱 PLG ) 」這個詞，有了一點概念後，開始在思考：「電商（不包括 Shopify, BigCommerce 等電商平台）也能是產品導向式成長的服務嗎？」</p><h3>什麼是產品導向式成長 ( Product-led Growth )</h3><p>對應過往的「業務導向式成長 ( Sales-led Growth ) 」、「行銷導向式成長 ( Marketing-led Growth ) 」，「產品導向式成長 ( Product-led Growth )」主張提供免費的產品試用期間，讓用戶親自體驗產品，透過良好的使用者體驗、解決用戶痛點等產品力，來吸引用戶、保留用戶，並讓用戶為產品宣傳，帶來更多的用戶，這也是所謂的「產品導向式成長飛輪」。常被用來作為產品導向式成長的代表服務包含了：Airtable, Slack, Figma。</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fHxSVPL-KblciTXdsvDblQ.png" /><figcaption>產品導向成長飛輪 Source: <a href="https://www.productled.org/foundations/what-is-product-led-growth">Product‑Led Growth Collective</a></figcaption></figure><p>在市場還不算太競爭前，一個服務一旦取得用戶以後，因為轉換成本或是選擇不多等原因，用戶比較容易留存；然而隨著市場趨於競爭，新的服務常常提供了更低的轉換成本、更好的產品功能，使得服務需要付出更大的努力來維持用戶的留存率，而在產品導向式成長上靠的就是提升產品力。</p><p>類似「產品導向式成長 ( Product-led Growth )」這種科技趨勢流行詞，不時地都會出現，它們反應了隨著市場變化逐漸成形的趨勢，可以提供一些新的刺激，讓人思考如何擷取一些元素納入自己面臨的情境裡。</p><h3>產品導向式成長的電商？</h3><p>看到這個概念以後，思考電商如何採納這樣的概念？電商本身已經是免費使用的軟體服務了，那有沒有可能不藉由折扣、活動、上架的商品，而是純粹透過「軟體功能的開發」來吸引消費者、保留消費者，並且藉著口碑帶來更多消費者？像是滾雪球一般，隨著每次產品的迭代都讓自己更加茁壯？如果可能，什麼樣的功能是可以期待的？我想「可購物的內容 ( Shoppable Content )」以及「社群商務 ( Social Commerce )」或許是值得觀察的。</p><h3>可購物的內容 ( Shoppable Content )</h3><p>可購物的內容 ( Shoppable Content ) 指的是在網路上看到的各種內容，不論是文章或是影片，使用者都能在看到內容的當下，直接將其中有興趣的商品導入電商的購物流程中。</p><p>2022 年初的時候，Instacart 與 TikTok、Hearst 內容媒體集團合作，推出使用者在瀏覽做菜影片或是食譜網站時，可以直接連到 Instacart 將食譜內的食材加入購物車，並且結帳。</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/730/0*s5uYrreyHFR_hMpt" /><figcaption>Source: <a href="https://techcrunch.com/2022/03/16/instacarts-new-shoppable-recipes-feature-lets-creators-link-shopping-lists-to-their-tiktok-videos/">TechCrunch</a></figcaption></figure><p>Walmart 也有同樣的做法，在多個內容媒體網站上，嵌入食材清單以及加入購物車的按鈕，<a href="https://www.today.com/food/today-table-launches-shoppable-recipes-walmart-t243486">在 TODAY 的網站上，甚至還可以選擇推薦的、最低價的、最不浪費的、有機的食材</a>組合：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DXSj-CKMFzVBCBlwdlH1fA.png" /><figcaption>Source: <a href="https://www.grocerydive.com/news/how-walmart-is-experimenting-with-shoppable-content/632983/">GROCERY DIVE</a></figcaption></figure><p><a href="https://www.yahoo.com/lifestyle/walmarts-shoppable-content-could-home-184258368.html">Tomorrow Retail Consulting 的研究</a>認為「可購物的食譜 ( Shoppable Recipe )」對於主打 Grocery 產品的線上購物平台，是一個很強大的獲客工具，有機會佔到 5–8% 的營收。</p><p>除了嵌入內容媒體網站之外，Walmart 也與互動影片開發公司 Eko 合作了一個 <a href="https://cookshop.eko.com/">COOKSHOP</a> 網站，為這個功能再提供了一個完備的資源。使用者可以在網站上看做菜的影片，與影片互動，在過程中選擇自己要的版本，像是下圖可以選擇要加咖啡，或是加巧克力的版本，點擊後影片就會往所選的版本繼續播放：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/598/1*A8y-Fn4p_WvUxJGTy12wCw.png" /><figcaption>Source: <a href="https://cookshop.eko.com/">COOKSHOP</a></figcaption></figure><p>影片播放完後，可以點擊 Shop your ingredients</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/562/1*Xw8uqDwnsze2qNJe96lV8g.png" /><figcaption>Source: <a href="https://cookshop.eko.com/">COOKSHOP</a></figcaption></figure><p>會跳出一個畫面，輸入你所在的郵遞區號來找到最近的 Walmart：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LkjHc1tBLTwyW8MvqBj8Vw.png" /><figcaption>Source: <a href="https://cookshop.eko.com/">COOKSHOP</a></figcaption></figure><p>除此之外，<a href="https://cookshop.eko.com/">COOKSHOP</a> 網站也<a href="https://www.marketingdive.com/news/walmart-expands-shoppable-video-hub-with-celebrity-cooking-shows/595689/">積極與名人合作內容的產製</a>：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nmtbwtf85LbCLEkytPL6AA.png" /><figcaption>Source: <a href="https://cookshop.eko.com/">COOKSHOP</a></figcaption></figure><p>在這些可購物的內容 ( Shoppable Content ) 中，不論是網站食譜或是做菜影片，都有一個關鍵要素是提供內容的「創作者」，自然地就把「社群商務 ( Social Commerce ) 」這個概念也帶了出來。</p><h3>社群商務 ( Social Commerce )</h3><p>以個人或小團體作為一個影片創作單位，並且提供穩定獲利模式的 YouTube 已然成為大眾休閒娛樂中不容忽視的一塊。近年來以影片為基礎的社群軟體，如 Instagram、TikTok 逐漸成為主流。許多意見領袖 ( Influencer ) 在這些平台上建立起自己的個人品牌，其中有些人藉著直播或是導購平台，同時為電商以及個人都帶來了獲利。<a href="https://corporate.walmart.com/newsroom/2021/11/22/walmart-celebrates-one-year-of-livestreams-with-first-shoppable-livestream-on-twitter">Walmart 也在直播上積極部署</a>，與 TikTok, Twitter 等平台進行「可購物的直播 ( Shoppable Livestream )」。這些都是社群商務 ( Social Commerce ) 的實踐之一。</p><p>什麼是社群商務 ( Social Commerce)？Accenture 提供了一個定義 “The integration of social experiences and ecommerce transactions in a single path to purchase, enabled by a platform.” 以及分類：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/724/0*LAx0ZrfA1lDeGkg-.png" /><figcaption>Source: <a href="https://www.accenture.com/us-en/insights/software-platforms/why-shopping-set-social-revolution?c=acn_glb_socialcommercebusinesswire_12755585&amp;n=mrl_0122">Accenture</a></figcaption></figure><p>在<a href="https://www.accenture.com/us-en/insights/software-platforms/why-shopping-set-social-revolution?c=acn_glb_socialcommercebusinesswire_12755585&amp;n=mrl_0122"> Accenture 的報告</a>中，預估在 2025 年以前，社群商務 ( Social Commerce ) 將會佔總體電商營收的 16.7%。報告中有一張社群商務生態系的架構圖：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/724/0*_FzMe2F-9jYVVn86.png" /><figcaption>Source: <a href="https://www.accenture.com/us-en/insights/software-platforms/why-shopping-set-social-revolution?c=acn_glb_socialcommercebusinesswire_12755585&amp;n=mrl_0122">Accenture</a></figcaption></figure><p>顯然，已經有不少 Player 開始佈局當中 Platforms 的角色。<a href="https://www.marketingdive.com/news/tiktok-social-commerce-ad-formats/629990/">平台方如 TikTok 為社群商務 ( Social Commerce ) 新增了更多的廣告格式</a>。BNPL 平台 <a href="https://techcrunch.com/2022/10/19/klarna-launches-new-creator-features-and-shoppable-video/">Klarna 近期也推出了 Creator Platform</a>，媒合電商與創作者。電商可以透過平台聯繫創作者，創作者可以在平台上設定自己的佣金 (Commision) 比例，並且擁有一個統一的平台，管理檢視自己導購的效益：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/676/1*BuMQUFisZyLkoriBlTcDcQ.png" /><figcaption>Source: <a href="https://www.klarna.com/us/business/marketing-solutions/creator-platform/">Klarna</a></figcaption></figure><p><a href="https://corporate.walmart.com/newsroom/2022/10/18/walmart-launches-new-content-creator-platform">Walmart 也在近期推出了自己的 Creator Platform</a>，相信結合現有的 Walmart Connect，以及 Walmart 在可購物的內容 ( Shoppable Content ) 上的佈局，應該可以有一個顯著的綜效。</p><h3>總結</h3><p>Walmart 近期為年末購物潮做好全面準備，除了上述內容以外，也<a href="https://corporate.walmart.com/newsroom/2022/10/27/walmart-welcomes-customers-with-a-streamlined-digital-shopping-experience">與 Stylitics 合作推出 AI 推薦服裝搭配組合，利用 AR 技術讓消費者模擬家電擺在家中的實際效果</a>，<a href="https://corporate.walmart.com/newsroom/2022/10/27/walmart-and-netflix-premiere-in-store-expansion-of-popular-netflix-hub">與 Netflix 合作推出 Netflix 專區</a>等等。看著零售龍頭大規模的佈局，讓我相信電商也能像產品導向式成長的服務一般，藉著軟體功能的開發，吸引更多消費者、保留消費者，並且讓消費者帶來更多消費者。</p><p>一般的「產品導向式成長」似乎比較指稱在產品本身環境內的開發，而分享的這些案例，更著力於從產品以外的環境，帶入產品本身的環節。但藉著產品軟體功能的開發，來吸引更多人、帶來更多營收的本質是一致的，也想藉著這個概念來思考零售業在價格戰以外的其他選擇。</p><p>透過「可購物的內容 ( Shoppable Content ) 」結合「社群商務 ( Social Commerce ) 」，消費者不再是主動地想到需要什麼，或是收到活動優惠的訊息才進到電商網站，而是在日常瀏覽社群平台、媒體平台時，就能從自身黏著度較高的意見領袖 ( Influencer ) 上看到商品，並且直接完成購物，同時也能為自身喜愛的意見領袖 ( Influencer ) 提供更多創作的資源。</p><p>同時也在想，現在電商靠著 Product Feed 來與廣告、導購網站做介接，消費者可以從外站，連到電商網站的商品專頁 ( Product Detail Page )，然後進行結帳。如果往後「可購物的內容 ( Shoppable Content ) 」、「社群商務 ( Social Commerce ) 」發展得更成熟時，也許電商網站前端介面的重要性會再降低，特別是首頁，從「首頁 → 商品目錄頁 → 商品專頁 → 購物車頁 → 結帳」的流程，變成 「商品專頁 → 購物車頁 → 結帳」或是「購物車頁 → 結帳」成為主要線上購物流程。特別是「購物車頁 → 結帳」也許有機會與合作平台用 API 對接，直接在該平台完成購物流程，連電商網站都不用進去，我想這也是最近另一個科技趨勢流行詞「<a href="https://www.salesforce.com/blog/define-headless-commerce/">無頭式電商 ( Headless Commerce ) 」 </a>出現的原因。</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c528c0c9c96a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[寫在開始更新之前]]></title>
            <link>https://peilee-98185.medium.com/%E5%AF%AB%E5%9C%A8%E9%96%8B%E5%A7%8B%E6%9B%B4%E6%96%B0%E4%B9%8B%E5%89%8D-9c5f5e26fa55?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/9c5f5e26fa55</guid>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Wed, 02 Nov 2022 07:34:32 GMT</pubDate>
            <atom:updated>2022-11-02T07:34:32.131Z</atom:updated>
            <content:encoded><![CDATA[<p>Medium 已經停更許久，回顧了一下之前的文章數：</p><p>2017 年 21 篇</p><p>2018 年 19 篇</p><p>2019 年 7 篇</p><p>2020 年 0 篇</p><p>2021 年 2 篇</p><p>這幾年一直都有回來寫文章的念頭，但遲遲沒有執行，我想原因在於工作角色的轉變。2017 — 2019 年主要做的是 Data Application Engineer + Data Analyst 的角色，當時的文章是當作筆記來使用，然後順手發佈分享。</p><p>2020 年開始，工作上的角色更偏向 Project Manager + Project Oriented Technical Support，就是個要想盡辦法讓專案順利完成的角色。2022 年開始，角色轉變為 Data Team Lead，從 0 到 1 建立了 Data Team，包含：團隊成員招募與管理、擬定 Data Strategy、編列預算，帶領團隊進行 Data Product 的持續迭代，推行 Data Project 讓 Data 更深入業務核心，幫助公司成長。</p><p>雖然不時地還是有執行一些 Hands-on 的工作，但已經離實作端有點距離，沒有辦法主題連貫地發佈以前偏向 Tutorial 的內容。曾經想過是不是把以前的文章砍掉重新來過，畢竟以前的文章都只是隨手筆記，有些開了頭根本沒收尾，或是已經不符合現在常用的版本，但陸陸續續都還是有人來看，也有人因此而 Follow，我想就還是保留下來，如果能夠對部分的人有一點幫助就好了。</p><p>接下來的文章很難說有一個「主題」，比較像是為了 Push 自己平時好好閱讀、好好做筆記，並且定時整理一陣子一陣子看的東西，做個輸出。想藉著這樣的過程 Push 自己思考，並且定期回頭檢視當時思考的結果。預計頻率是從一個月一篇開始。Ok, here we go.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9c5f5e26fa55" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[讀書筆記：資料處理流程]]></title>
            <link>https://peilee-98185.medium.com/%E8%AE%80%E6%9B%B8%E7%AD%86%E8%A8%98-%E8%B3%87%E6%96%99%E8%99%95%E7%90%86%E6%B5%81%E7%A8%8B-d43a4b8d9c9b?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/d43a4b8d9c9b</guid>
            <category><![CDATA[data-wrangling]]></category>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Sun, 07 Mar 2021 07:25:48 GMT</pubDate>
            <atom:updated>2021-03-07T07:25:48.655Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Notes of Chapter 3 The Dynamics of Data Wrangling in Principles of Data Wangling</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*vwEpIyGPI53U2ufe.png" /><figcaption><a href="https://learning.oreilly.com/library/view/principles-of-data/9781491938911/">https://learning.oreilly.com/library/view/principles-of-data/9781491938911/</a></figcaption></figure><p>這章主要敘述在資料處理流程中，需要的資料轉換和結果描述，以及在資料服務流程架構中的運作。</p><h3>一、資料處理流程</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/963/0*LOiifDbIq1gDJVCv.png" /><figcaption><em>Principles of Data Wangling Figure 3–1</em></figcaption></figure><h4>1. Accessing</h4><ul><li>取得權限</li><li>移動、複製資料</li><li>分析資料集之間的關係，如資料重複、資料衝突</li></ul><h4>2. Transforming</h4><ul><li>調整資料的結構、粒度、正確性、時間性、範圍</li></ul><h4>3. Profiling</h4><ul><li>調整資料的結果需要有意義的回饋</li></ul><h4>4. Publishing</h4><ul><li>發布出去的東西，如資料轉換後的資料集、資料轉換的紀錄檔 (Log)、描述資料的資料集</li></ul><h4>額外的面向：子集合、抽樣</h4><p>子集合和抽樣是在資料處理流程中很有效率的方式。</p><p>在資料中如果包含了異質性的資料，作法是將他拆成多個子集合，分別做資料轉換後再合併起來。</p><p>假設今天是一個巨量的資料，任何轉換都需要非常多的時間及資源，並且事後需要大量人工去做確認的話，較佳的作法是在資料中做抽樣，以加速資料處理流程。理想上來說每一個 Transforming 搭配著處理結果的 Profiling 要能在 100 milliseconds 內完成，然而有時會到幾秒鐘。</p><p>在抽樣方法也是一個在統計學及市場調查課本中很廣泛討論的議題，例如 Leslie Kish’s <em>Survey Sampling</em>。也有很多關於如何從巨量資料中抽取不同種類樣本的技巧，例如 <em>Synopses for Massive Data: Samples, Histograms, Wavelets, Sketches</em> by Cormode et al. 另外也有許多軟體套件及資料庫實際提供了這些方法在裡面。</p><h3>二、主要的資料轉換及資料輪廓描述</h3><h4>1. 資料轉換</h4><p>(1) Structuring：改變資料格式或結構的動作</p><ul><li>移動紀錄或欄位的位置</li><li>將一個欄位拆成多個欄位、將多個欄位合併成單一欄位</li><li>移除特定子集合資料</li><li>匯總（Aggregating）</li><li>將欄位值移至欄位（Pivoting）</li></ul><p>(2) Enriching：新增資料至資料集的動作</p><ul><li>水平合併資料（Join）</li><li>垂直合併資料（Union）</li><li>插入資料描述相關資訊（Metadata），例如資料建立時間、資料轉換的操作者、資料檔案名稱、資料位置等等</li><li>根據某一欄位建立新欄位</li></ul><p>(3) Cleansing：修正異常資料的動作</p><ul><li>修復空值</li></ul><h4>2. 資料輪廓描述</h4><p>(1) 單一值（Individual Values）：確認每個欄位值的合理性</p><ul><li>值的形式是否符合規範，例如日期須為 YYYY-MM-DD</li><li>值的意義是否符合業務邏輯</li></ul><p>(2) 組合值（Set-based）：確認每個欄位值的分佈</p><ul><li>在單一欄位或相關的多個欄位中，值分佈的形狀跟程度</li></ul><p>在做完資料轉換以及結果描述之後，我們應該會有資料轉換邏輯對應的 Script 以及結果描述的步驟。接著優化，讓資料轉換的 Script 運作得更有效率更精確，於是我們得開始追蹤紀錄各個版本的 Script 以及修訂內容，而為了紀錄這些，我們可能需要另一個客制的資料輪廓描述資訊。</p><h3>三、在資料服務流程架構中的資料處理流程</h3><h4>1. 吃資料</h4><ul><li>Transforming：執行最小量的資料轉換，讓資料能夠做 Metadata 分析以及之後的精煉。</li><li>Profiling：確認沒有掉資料，且資料品質問題得到修正。</li></ul><h4>2. 描述資料</h4><ul><li>Transforming：在描述資料時需要對資料有廣泛的理解，因此需要做探索性的資料轉換，例如：將資料值拆開來評估資料品質及一致性、篩選資料用個別子集合評估資料範圍及正確性、資料匯總（Aggregating）及將欄位值移至欄位（Pivoting）。</li></ul><h4>3. 評估資料</h4><ul><li>Transforming：當要新增資料集時，必須是評估新資料集與現有資料集是否有可能做垂直合併（Union），或是水平合併（Join）。而在水平合併時，則可能出現合併後的空值過多，或是多對多的問題。在做這些動作時，都會需要不少資料清理以及轉換。</li><li>Profiling：利用 Set-based 的資料輪廓描述，可以提供一些基本的資料合併回饋。</li></ul><h4>4. 設計並建立精煉的資料</h4><ul><li>Transforming：</li></ul><p>(1) Structuring：調整資料結構及範圍至符合特定分析目的能增進效率，這時可能會需要做一些 Aggregate 及 Pivot。</p><p>(2) Enriching：利用垂直合併或水平合併，合併多個資料來源。另外若是有很頻繁使用的一些須經計算的欄位，也可以在這時建立新的欄位在資料集裡面。</p><p>(3) Cleaning：標示不正確或不一致的資料，或是用其他方式取代錯誤的值及空值。</p><ul><li>Profiling：</li></ul><p>(1) 單一值（Individual Values）：為了確保每個欄位的資料品質及一致性，必須確實確認每個欄位的值，一開始比較能確認的是值的形式，因為在對資料還不熟悉時，值的意義及合理範圍一開始比較無從確認，必須要等產出更多的資料分析報表，對資料有更多了解之後，再回來新增確認步驟。</p><p>(2) 組合值（Set-based）：如同確認值的意義及合理範圍一樣，一開始只能做一些簡單的分佈確認，必須等到對資料有更多的了解之後，欄位之間的關聯以及值的趨勢變化等等才有可能被納入。</p><h4>5. 特定需求的資料分析</h4><ul><li>Transforming：</li></ul><p>(1) Structuring：拉出所需要的子資料集、排除不需要的資料、匯總、Pivoting。</p><p>(2) Enriching：垂直或水平合併、新增複雜計算的欄位。</p><h4>6. 建模與預測</h4><ul><li>Transforming：針對類別型欄位的 Pivoting，以及數值型欄位值的正規化。</li><li>Profiling：在建模與預測時，有個好處是能夠偵測到離群值，這些離群值有可能是需要清理的錯誤資料，也有可能是合理的資料，而進而增加我們對資料的理解。</li></ul><h4>7. 建立優化過後的資料集</h4><p>相對於設計並建立精煉的資料同等重視 Transforming 及 Profiling，建立優化過後的資料集更重視 Profiling。</p><p>只有在新建資料的時候去確認資料轉換的邏輯是否正確是不夠的，資料轉換的 Script 需要能被持續自動地轉換新輸入的資料，因此必須要持續追蹤未來每個輸入值的變化，以及轉換後的正確性。包括需要去確認每個子資料集的資料分佈，評估資料值的分佈範圍以及分佈形狀的變化。</p><h4>8. 例行性的資料分析與資料服務</h4><p>如同相對於設計並建立精煉的資料同等重視 Transforming 及 Profiling，建立優化過後的資料集更重視 Profiling；例行性的資料分析與資料服務也相對於特定需求的資料分析更要求有效率地正確地去轉換新輸入的資料，以隨時取得正確的結果。</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d43a4b8d9c9b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[讀書筆記：資料服務的流程架構]]></title>
            <link>https://peilee-98185.medium.com/%E8%AE%80%E6%9B%B8%E7%AD%86%E8%A8%98-%E8%B3%87%E6%96%99%E6%9C%8D%E5%8B%99%E7%9A%84%E6%B5%81%E7%A8%8B%E6%9E%B6%E6%A7%8B-83b273f82a06?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/83b273f82a06</guid>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[data-wrangling]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Fri, 19 Feb 2021 08:03:55 GMT</pubDate>
            <atom:updated>2021-02-22T06:59:07.259Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Notes of Chapter 2 A Data Workflow Framework in Principles of Data Wangling</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Mif-Warj4_nWyI8QaVsnTg.png" /><figcaption><a href="https://learning.oreilly.com/library/view/principles-of-data/9781491938911/">https://learning.oreilly.com/library/view/principles-of-data/9781491938911/</a></figcaption></figure><h3>一、資料價值的傳遞</h3><h4>1. 間接：人為</h4><p>由一群富有知識以及經驗的人，將現有的資料以他們的理解框架詮釋過後，傳遞給組織以幫助決策，例如保險業的風險評估模型。</p><h4>2. 直接：自動</h4><p>藉由程式開發的資料處理流程，自動將資料餵給數據驅動的系統，例如 Netflix 上的推薦系統。</p><p>要能做到資料傳遞的自動化，必須要先能人為做到。書中提到人在這當中擔任的角色，主要是確認「資料裡面有什麼」以及「資料的品質是否合格」。另外，我認為人也擔任了一個發散再歸納的角色，必須在面對各種情境的時候，一一找到對應的解決方案，然後歸納經驗並程式化。程式出錯時再回去修正邏輯，藉由這樣反覆來回修正，一步步優化資料處理流程，最終始能做到自動化。</p><p>而作者將這樣的過程，拆為三個階段並敘述各階段目的：</p><h4>1. Raw Stage</h4><p>吃資料、資料探索及描述。</p><h4>2. Refined Stage</h4><p>設計能符合所有資料的處理流程、執行分析、建模及預測。</p><h4>3. Production Stage</h4><p>建立 Production 等級的資料品質、建立例行性報表以及自動化資料產品/服務。</p><p>將資料處理流程由 IT 自動化，有兩個優點：</p><ol><li>符合數據治理（Data Governance）的基本原則，中央統一處理。</li><li>提升資料使用的效率，IT 準備好材料，由其他部門廣泛地重複使用。</li></ol><p>而放到實際環境中主要面臨兩個問題：</p><ol><li>需要大量的資料處理時間。</li><li>人數的不對等，時常只有少數的幾個 IT 面對大量的分析人員。</li></ol><p>在做資料價值傳遞時首要必須考量的兩個點：</p><ol><li>傳遞出來的價值要是能夠被執行的。</li><li>盡可能地提升資料處理流程的效率。</li></ol><h3>二、Raw Stage</h3><ul><li>主要動作：吃資料</li><li>主要輸出：描述資料、評估資料可用性</li></ul><h4>1. 吃資料</h4><p>資料傳遞的方式由簡單到複雜：</p><p>(1) Email, FTP, 共享網路的資料夾</p><p>(2) Alteryx, Talend, Informatica Cloud</p><ul><li>優點：非工程師也有能力做設定及維護</li></ul><p>(3) Sqoop, Flume, Kafka：</p><ul><li>優點：可取得更即時、資料粒度更細的資料</li><li>缺點：軟體開發及維護成本</li></ul><p>資料儲存的方式從不彈性到彈性：</p><p>(1) Relational Database 關聯式資料庫：將資料存入預先定義好架構的地方 schema-on-write。</p><p>(2) NoSQL 資料庫：讓資料以較有彈性的方式存入，例如有 MongoDB, Cassandra。</p><p>(3) Distributed Filesystem 分散式檔案系統：讓使用者能夠自由新增、修改、複製、移動資料，並針對個別資料給予不同權限，不需要事先定義好結構 schema-on-read，例如有 HDFS, Amazon S3。</p><p>來自不同合作夥伴的資料，一般在 Raw Stage 會分別存在不同的資料集，並在 Refined Stage 將這些資料以統一的標準及格式做整理。</p><h4>2. 資料探索及描述</h4><p>在描述資料時，有幾個關鍵面向：</p><p>(1) Structure 結構</p><ul><li>每筆紀錄的欄位是否相同？相同 CSV，不相同適用 XML 跟 JSON。</li><li>每個欄位是單一值還是多值？</li><li>每個欄位的資料型態</li><li>文字編碼、是否被 Hash、時區、日期時間格式、幣別</li></ul><p>(2) Granularity 粒度</p><ul><li>每筆紀錄的主體，例如每個顧客在每間店的交易、每間店每天的營收、每區每週的營收。</li></ul><p>(3) Accuracy 正確性</p><ul><li>資料品質：正確性 &amp; 一致性</li><li>常見的資料不正確問題：產品編碼不全、不同產品共用同一個產品編碼、產品編碼過期、拼錯字、沒有正確分類、數值異常過大過小、日期時間格式不一致、資料筆數異常過多過少、電話格式、地址格式、Email 格式。</li></ul><p>(4) Temporality 時間性</p><ul><li>資料什麼時候被蒐集？</li><li>每筆紀錄被建立之後是否還會更新？是否有更新的時間戳記？</li><li>如何判定資料是舊的？紀錄有衝突時是否能用時間戳記判斷何為真？</li></ul><p>(5) Scope 範圍</p><ul><li>每筆紀錄的主體包含了什麼特徵？不包含什麼特徵？</li><li>每個欄位與外部資料的關聯性？</li><li>欄位之間彼此是否一致？例如生日跟年齡，交易商品金額加總後與交易總金額。</li><li>是否有遺失的資料？是隨機性遺失或是系統性遺失？</li><li>每筆紀錄的主體是否有重複的資料？是否改變了資料粒度？是否在分析前需要去除重複？</li><li>每筆紀錄是否為同質性的資料？如果不是，異質性資料之間的關聯性是？</li></ul><h3>三、Refined Stage</h3><ul><li>主要動作：設計並準備精煉過後的資料、特定的分析、建模及預測</li><li>主要輸出：能夠立即運用至各種分析的資料、有用的資訊及洞見</li></ul><h4>1. 設計精煉的資料</h4><p>在 Refined 階段，在各個關鍵面向可能遇到的問題及解決方法：</p><p>(1) Structure 結構</p><p>在各個分析及視覺化工具中，皆以 table 作為主要輸入，必要時須將 Raw Data 轉換成 table。在建模及預測時，則需將類別型變數做轉置的動作。</p><p>(2) Granularity 粒度</p><p>用不同資料粒度儲存同一個資料集，視需求儲存多個版本能夠幫助直接的分析。</p><p>(3) Accuracy 正確性</p><p>處理欄位內錯誤資料的基本三個方式：</p><ul><li>刪除有錯誤資料的紀錄</li><li>保留有錯誤資料的紀錄，但標示不正確</li><li>用預設值或是推估的值取代錯誤的值</li></ul><p>一般常見的錯誤會出現在欄位之間彼此衝突，而解決這個衝突最常用的方法是利用<strong>時間戳記</strong>來判斷。也有一些情境用其他標記來判斷會更適合，例如軟體的版本版號。</p><p>(4) Scope 範圍</p><p>設計資料的範圍時，要確認資料擁有需求的欄位，例如假設客戶相關的資料分散在很多張 table 裡面，就可以視需求把他們併在一起。</p><h4>2. 特定的分析、建模及預測</h4><p>特定的分析一般以報表、簡報或是 Dashboard 來呈現，用以回答過去或是現在的問題；而建模及預測則用來回答未來的問題，或是用以了解結果與各個特徵之間的關聯性。</p><h3>四、Production Stage</h3><p>當 Refined Stage 中特定的分析、建模及預測，被需求單位認為是需要持續更新追蹤結果的時候，就需要將這些一次性的應用系統化，用一個固定的流程去開發、排程、監控這些資料的結果。</p><h4>1. 建立優化後的資料</h4><p>相對於 Refined Stage 設計精煉的資料，Production Stage 則需要針對更特定的分析需求，去優化資料格式、簡化資料，讓資料能夠更正確、有效率地支持各種分析。</p><h4>2. 建立例行性的資料服務</h4><p>建立例行性的資料服務，仰賴持續地監控資料流程，並確保在結構、粒度、正確性、範圍都能維持穩定的品質。當新的資料持續地進來，按照同一個邏輯去轉換資料時，難免會有舊的邏輯與新的資料不符合的情形發生，例如新資料的正確性出問題、有欄位被移除或是變空值、重複的資料、資料部分遺失等等。這些邏輯的來回修正，都是優化資料流程必要的動作。</p><h3>五、Pei 心得</h3><p>在接收、處理各種資料到開發資料服務時總會遇到不同的狀況，能有一群經驗豐富的作者寫成一個架構，用較全面性的視角來看，覺得有被提醒到不少事情，像是描述資料的文件應該包含什麼面向，確認資料時應該確認哪些點。除此之外，對整個資料服務的架構有了比較明確的階段性定義，可以知道目前自己手上的東西在哪一個階段，什麼應該往 Production 進行，什麼應該保留在 Refined 的階段，每個階段依該發揮什麼功能等等。可以有的 action 包括：</p><ul><li>列出所有資料來源並建立文件描述資料</li><li>建立每一段資料流程的確認項目</li><li>列出所有資料服務並確認所處階段</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=83b273f82a06" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[2019 Dec GCP Summit 摘要]]></title>
            <link>https://peilee-98185.medium.com/2019-dec-gcp-summit-%E6%91%98%E8%A6%81-279f6b5a0baf?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/279f6b5a0baf</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[cloud-services]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[cloud]]></category>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Tue, 17 Dec 2019 07:49:04 GMT</pubDate>
            <atom:updated>2019-12-17T07:49:04.151Z</atom:updated>
            <content:encoded><![CDATA[<p><em>簡單摘要 2019/12/10 Best of Google Cloud Summits 的其中兩個主題：</em></p><ul><li><em>Cloud AI</em></li><li><em>Data Integration Tool</em></li></ul><h3>Cloud AI</h3><h4>一、顧客服務解決方案</h4><ol><li><a href="https://dialogflow.com/">Dialogflow</a>：</li></ol><ul><li>提供跨平台的問答機器人服務，支援語音及文字的輸入。</li><li>有 UI 介面的後台可進行設定操作。</li></ul><p>2. <a href="https://cloud.google.com/solutions/contact-center/">Contact Center AI</a></p><ul><li>將 Dialogflow 納入服務項目之一。</li><li>對顧客的問題進行主題分類，即時提供對應的文件，增進客服效率。</li></ul><h4>二、客製化模型</h4><ol><li>AutoML Vision：圖像辨識。多了一個 Edge 的選項，可以 Export 出來供 Offline 使用。</li><li>AutoML Video Intelligence：影像辨識</li><li>AutoML Natural Language：自然語言</li><li>AutoML Translation：翻譯</li><li><a href="https://cloud.google.com/automl-tables/?hl=zh-tw">AutoML Tables</a>：還在 Beta 版。提供直覺的 UI 介面做建模、模型評估。</li></ol><ul><li>Import：目前支援 BigQuery, Storage CSV 檔, Upload 本機檔案。</li><li>Train：有簡單的每個欄位資料分佈的圖表。</li><li>Models：建模時只需要輸入 Budget。進階設定可以選評估指標（AUC, Recall 等等）。</li><li>Evaluate：產出評估指標的數值，以及特徵重要性圖表。</li><li>Test &amp; Use：支援 Batch Prediction、Online Prediction ( API ), Export Model ( As Docker Container )。</li></ul><p>6. <a href="https://cloud.google.com/recommendations/?hl=zh-tw">Recommendation AI</a>：還在 Beta 版。提供客製化的推薦系統。</p><ul><li>Ingest Data</li><li>Customize Your Recommendation：可以選擇推薦類型、目標，並設定商業邏輯。</li><li>Deliver：API</li></ul><h3>Data Integration Tool</h3><h4>一、選擇工具的考量點</h4><ol><li>Migration</li><li>Batch / Streaming</li><li>Team Technical Skill Set</li><li>Configurable Autoscaling or Full Serverless</li></ol><h4>二、GCP Data Integration Tool</h4><p><em>（按技術能力要求由高到低排序）</em></p><ol><li>Dataflow：技術能力要求最高</li></ol><ul><li>Apache Beam</li><li>執行 Realtime 資料處理</li><li>Streaming &amp; Batch</li></ul><p>2. Data Fusion：這次 Summit 在這個主題 Google 主推的服務，值得期待。</p><ul><li>CDAP</li><li>執行資料的 ETL</li><li>可涵蓋 Dataproc ( Hadoop, Spark ) 及 Composer ( Airflow, Data Orchestration ) 功能</li><li>Streaming &amp; Batch</li></ul><p>3. Dataprep：技術能力要求最低</p><ul><li>Trifacta</li><li>執行資料的 ETL</li><li>only Batch</li></ul><h4>三、Data Fusion</h4><ol><li>Data Preparation：Data Profiling, Point &amp; Click Wrangling</li><li>Self Service Integration：</li></ol><ul><li>提供各平台的 Connector：</li></ul><p>(1) GCP : BigQuery, Datastore, Storage, Spanner, PubSub</p><p>(2) AWS : S3, Kinesis, Redshift</p><p>(3) Azure : Data Lake, Blob Store</p><p>(4) On-prem : SQL Server, Oracle DB, IBM DB2, IBM NETEZZA, Excel</p><p>(5) Open Source : Hadoop, Spark, MySQL, HIVE, KUDU</p><p>(6) SaaS : Salesforce</p><ul><li>Code Free 不需寫程式，但需要的話可以用 Javascript</li></ul><p>3. Orchestration：</p><ul><li>可選擇要用 Mapreduce 還是 Spark Engine 跑</li><li>可以排程 &amp; 用事件觸發下個步驟</li></ul><p>4. Governance</p><p>個人覺得 Dialogflow 跟 Contact Center AI 在企業的客戶服務上值得嘗試。客製化模型 AutoML Tables 及 Recommendation AI 雖然都還在 Beta 版，但目前台灣有些在做這塊的新創可能會面臨不小的危機。最後是 Data Fusion 的 ETL 服務，對於不同平台的整合做得更好、功能也更加強大了，跟 Google 內部工程師聊的時候他也是推薦這套，相當值得期待。</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=279f6b5a0baf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ㄘㄘㄘㄜㄘㄜㄘㄜㄕㄘㄜㄕ測試]]></title>
            <link>https://medium.com/series/%E3%84%98%E3%84%98%E3%84%98%E3%84%9C%E3%84%98%E3%84%9C%E3%84%98%E3%84%9C%E3%84%95%E3%84%98%E3%84%9C%E3%84%95%E6%B8%AC%E8%A9%A6-ba6b2392e767?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/ba6b2392e767</guid>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Sat, 06 Apr 2019 09:42:45 GMT</pubDate>
            <atom:updated>2019-04-06T09:42:45.297Z</atom:updated>
            <content:encoded><![CDATA[<h3>測試</h3><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ba6b2392e767" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery ML 分類模型]]></title>
            <link>https://peilee-98185.medium.com/bigquery-ml-%E5%88%86%E9%A1%9E%E6%A8%A1%E5%9E%8B-135697d134ef?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/135697d134ef</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Mon, 01 Apr 2019 13:50:04 GMT</pubDate>
            <atom:updated>2019-04-01T13:50:04.219Z</atom:updated>
            <content:encoded><![CDATA[<p>用 BigQuery 的機器學習做顧客分類，預測<strong>顧客未來是否會購物</strong>。這邊資料集用 BigQuery 上的公開資料 data-to-insights。</p><h3>一、資料探索</h3><ul><li>實際進行過購物行為的顧客佔所有顧客的幾 %？</li><li>最暢銷的前五大商品？</li><li>有多少比例的顧客會回購？</li></ul><h3>二、預測標的</h3><p>顧客未來是否會購物</p><h3>三、選擇特徵並建立訓練資料集</h3><p><strong>選擇特徵</strong>是機器學習非常重要的一環，因為模型最好也只能跟你給的 input 資料一樣好，如果給的特徵的資訊不足以讓模型學習到特徵跟標的之間的關係，就沒辦法取得準確的模型。這邊先選擇兩個特徵：</p><ul><li>跳出次數</li><li>停留時間</li></ul><pre>#standardSQL <br>SELECT * EXCEPT(fullVisitorId) <br>FROM    # features    <br>    (<br>    SELECT fullVisitorId,     <br>           IFNULL(totals.bounces, 0) AS bounces,     <br>           IFNULL(totals.timeOnSite, 0) AS time_on_site   <br>    FROM `data-to-insights.ecommerce.web_analytics`   <br>    WHERE totals.newVisits = 1<br>    ) <br>JOIN ( <br>    SELECT fullvisitorid,     <br>           IF(COUNTIF(totals.transactions &gt; 0 AND <br>                              totals.newVisits IS NULL) &gt; 0, 1, 0) <br>           AS will_buy_on_return_visit   <br>    FROM `data-to-insights.ecommerce.web_analytics`   <br>    GROUP BY fullvisitorid<br>    )   <br>USING (fullVisitorId) <br>ORDER BY time_on_site DESC <br>LIMIT 10;</pre><ul><li>EXCEPT ()：排除欄位</li><li>USING ()：在大部分情況下等同於 ON，但在 SELECT * 時會有一些狀況，詳情可看<a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax">官方文件</a>。</li></ul><h3>四、建立模型</h3><p>目前有兩種模型可以用：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wLWYrpHc7Me2ghR5dgW6SA.png" /><figcaption>來源：QwikLabs</figcaption></figure><p>第一個是用<strong>線性迴歸</strong>預測數值，第二個是用<strong>羅吉斯迴歸</strong>做分類，這邊我們要用的是後者，語法如下：</p><pre>#standardSQL  <br><strong>CREATE OR REPLACE MODEL</strong> `ecommerce.classification_model` <br><strong>OPTIONS ( <br>    model_type=&#39;logistic_reg&#39;, <br>    labels = [&#39;will_buy_on_return_visit&#39;] <br>    ) </strong><br><strong>AS</strong>  <br>#standardSQL <br>SELECT * EXCEPT(fullVisitorId) <br>FROM    # features   <br>    (<br>    SELECT fullVisitorId,     <br>           IFNULL(totals.bounces, 0) AS bounces,     <br>           IFNULL(totals.timeOnSite, 0) AS time_on_site   <br>    FROM `data-to-insights.ecommerce.web_analytics`   <br>    WHERE totals.newVisits = 1     <br>    AND date BETWEEN &#39;20160801&#39; AND &#39;20170430&#39;<br>    ) <br>    # train on first 9 months   <br>JOIN (<br>    SELECT fullvisitorid,     <br>           IF(COUNTIF(totals.transactions &gt; 0 <br>                  AND totals.newVisits IS NULL) &gt; 0, 1, 0) <br>           AS will_buy_on_return_visit   <br>    FROM `data-to-insights.ecommerce.web_analytics`   <br>    GROUP BY fullvisitorid<br>    )   <br>USING (fullVisitorId) ;</pre><h3>五、評估模型</h3><p>在分類問題中，我們希望能夠最小化 False Positive Rate（猜顧客會買但他沒買）並且最大化 True Positive Rate（猜顧客會買而且他真的買了），這樣的關係我們會畫成 ROC 曲線來做視覺化，而在曲線下的面積（Area Under the Curve）我們叫做 AUC。在 BigQuery 中很神奇的是，AUC 是可以 Query 的！下面我們就來 Query 一下：</p><pre>#standardSQL <br>SELECT <strong>roc_auc</strong>,   <br>       CASE WHEN roc_auc &gt; .9 THEN &#39;good&#39;     <br>            WHEN roc_auc &gt; .8 THEN &#39;fair&#39;     <br>            WHEN roc_auc &gt; .7 THEN &#39;decent&#39;     <br>            WHEN roc_auc &gt; .6 THEN &#39;not great&#39;   <br>            ELSE &#39;poor&#39; <br>       END AS model_quality <br>FROM <strong>ML.EVALUATE( MODEL ecommerce.classification_model,</strong>  <br>                  ( SELECT * EXCEPT(fullVisitorId) <br>                    FROM    # features   <br>                        ( SELECT fullVisitorId,                                                      <br>                                 IFNULL(totals.bounces, 0) <br>                                 AS bounces,     <br>                                 IFNULL(totals.timeOnSite, 0) <br>                                 AS time_on_site   <br>                          FROM `data-to- <br>                                insights.ecommerce.web_analytics`   <br>                          WHERE totals.newVisits = 1     <br>                                AND date BETWEEN &#39;20170501&#39; <br>                                                    AND &#39;20170630&#39;) <br>                                # eval on 2 months   <br>                      JOIN ( SELECT fullvisitorid,     <br>                                    IF(COUNTIF(totals.transactions <br>                                    &gt; 0 AND totals.newVisits IS <br>                                    NULL) &gt; 0, 1, 0) <br>                                    AS will_buy_on_return_visit   <br>                              FROM `data-to-<br>                                   insights.ecommerce.web_analytics`   <br>                              GROUP BY fullvisitorid<br>                            )   <br>                      USING (fullVisitorId)  <br>));</pre><p>Query 結果：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/829/1*geJaKfA2l2HUv1AbI9uUGg.png" /></figure><h3>六、加強模型後再評估</h3><p>剛才結果不甚理想，這邊我們再新增特徵：</p><ul><li>顧客來源媒體</li><li>顧客來源國家</li><li>顧客使用裝置</li><li>初次造訪抵達的轉換階段</li></ul><pre>#standardSQL <br><strong>CREATE OR REPLACE MODEL</strong> `ecommerce.classification_model_2` <br><strong>OPTIONS (<br>　　　　model_type=&#39;logistic_reg&#39;, <br>　　　　labels = [&#39;will_buy_on_return_visit&#39;]<br>　　　　) </strong><br>AS  </pre><pre>WITH all_visitor_stats AS ( <br>    SELECT fullvisitorid,   <br>           IF(COUNTIF(totals.transactions &gt; 0 <br>              AND totals.newVisits IS NULL) &gt; 0, 1, 0) <br>           AS will_buy_on_return_visit   <br>    FROM `data-to-insights.ecommerce.web_analytics`   <br>    GROUP BY fullvisitorid <br>    )  </pre><pre># add in new features <br>SELECT * EXCEPT(unique_session_id) <br>FROM ( SELECT CONCAT(fullvisitorid, CAST(visitId AS STRING)) <br>              AS unique_session_id,        <br>             <strong> # labels </strong>      <br>              will_buy_on_return_visit,         <br>              MAX(CAST(h.eCommerceAction.action_type AS INT64)) <br>              AS latest_ecommerce_progress,        <br>             <strong> # behavior on the site </strong>      <br>              IFNULL(totals.bounces, 0) AS bounces,       <br>              IFNULL(totals.timeOnSite, 0) AS time_on_site,       <br>              totals.pageviews,        <br>              <strong># where the visitor came from</strong>        <br>              trafficSource.source,       <br>              trafficSource.medium,       <br>              channelGrouping,        <br>              <strong># mobile or desktop </strong>      <br>              device.deviceCategory,        <br>              <strong># geographic  </strong>     <br>              IFNULL(geoNetwork.country, &quot;&quot;) AS country    <br>       FROM `data-to-insights.ecommerce.web_analytics`,      <br>            UNNEST(hits) AS h      <br>       JOIN all_visitor_stats <br>       USING(fullvisitorid)    <br>       WHERE 1=1     <br>       # only predict for new visits     <br>       AND totals.newVisits = 1     <br>       AND date BETWEEN &#39;20160801&#39; AND &#39;20170430&#39; # train 9 months    <br>       GROUP BY unique_session_id, <br>                will_buy_on_return_visit,   <br>                bounces,   <br>                time_on_site,   <br>                totals.pageviews,   <br>                trafficSource.source,   <br>                trafficSource.medium,   <br>                channelGrouping,   <br>                device.deviceCategory,   <br>                country <br>    );</pre><p>接著來評估模型：（上面那串實在太大一沱了，取個變數名稱叫 [一大沱] 取代從 WITH 開始到結束的語法）</p><pre>#standardSQL <br>SELECT <strong>roc_auc</strong>,   <br>       CASE WHEN roc_auc &gt; .9 THEN &#39;good&#39;     <br>            WHEN roc_auc &gt; .8 THEN &#39;fair&#39;     <br>            WHEN roc_auc &gt; .7 THEN &#39;decent&#39;     <br>            WHEN roc_auc &gt; .6 THEN &#39;not great&#39;   <br>            ELSE &#39;poor&#39; <br>       END AS model_quality <br>FROM <strong>ML.EVALUATE(MODEL ecommerce.classification_model_2,</strong> ([一大沱]));</pre><p>結果：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/815/1*-mSjvREQvPKDs4f1hDkm1A.png" /></figure><p>0.91，到達了一個相當不錯的分數（滿分為 1）。</p><h3>七、預測</h3><p>得到滿意的模型後，下一步就是實際做預測了：</p><pre>#standardSQL<br>SELECT *<br>FROM <strong>ml.PREDICT(MODEL `ecommerce.classification_model_2`,</strong><br>   (<br>    WITH all_visitor_stats AS (<br>    SELECT fullvisitorid,<br>           IF(COUNTIF(totals.transactions &gt; 0 AND totals.newVisits <br>                     IS NULL) &gt; 0, 1, 0) AS will_buy_on_return_visit<br>    FROM `data-to-insights.ecommerce.web_analytics`<br>    GROUP BY fullvisitorid<br>        )</pre><pre>    SELECT CONCAT(fullvisitorid, &#39;-&#39;,CAST(visitId AS STRING)) <br>           AS unique_session_id,<br>           # labels<br>           will_buy_on_return_visit,<br>           MAX(CAST(h.eCommerceAction.action_type AS INT64)) <br>           AS latest_ecommerce_progress,<br>           # behavior on the site<br>           IFNULL(totals.bounces, 0) AS bounces,<br>           IFNULL(totals.timeOnSite, 0) AS time_on_site,<br>           totals.pageviews,<br>           # where the visitor came from<br>           trafficSource.source,<br>           trafficSource.medium,<br>           channelGrouping,<br>           # mobile or desktop<br>           device.deviceCategory,<br>           # geographic<br>           IFNULL(geoNetwork.country, &quot;&quot;) AS country<br>    FROM `data-to-insights.ecommerce.web_analytics`,<br>         UNNEST(hits) AS h<br>    JOIN all_visitor_stats <br>    USING(fullvisitorid)<br>    WHERE<br>    # only predict for new visits<br>    totals.newVisits = 1<br>    AND date BETWEEN &#39;20170701&#39; AND &#39;20170801&#39; # test 1 month<br>    GROUP BY unique_session_id,<br>             will_buy_on_return_visit,<br>             bounces,<br>             time_on_site,<br>             totals.pageviews,<br>             trafficSource.source,<br>             trafficSource.medium,<br>             channelGrouping,<br>             device.deviceCategory,<br>             country<br>    )<br>)<br>ORDER BY predicted_will_buy_on_return_visit DESC;</pre><p>執行結果：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/835/1*WSd2fqtrU9ZQyWl8kQq8PQ.png" /></figure><p>這邊新增了三個欄位，第一個 predicted_will_buy_on_return_visit 代表最後的預測結果，第二個 predicted_will_buy_on_return_visit_probs.label 只是列出標籤而已，第三個 predicted_will_buy_on_return_visit.prob 是每位顧客在該標籤的信心水準（confidence）。</p><p>以上。</p><p>一開始接觸機器學習的時候就是用 R 跟 Python，沒有想過有一天可以用下 Query 的方式來執行，操作起來覺得還算直覺，蠻不錯的，可惜的是現在模型的種類比較有限。不過以 Google 的進展來說，相信不久後也能有 Boosting 的模型可以用了吧。</p><p>看其他我寫的 BigQuery 文章：</p><ul><li><a href="https://medium.com/@peilee_98185/bigquery-%E8%B3%87%E6%96%99%E7%9A%84%E5%8C%AF%E5%85%A5%E5%8C%AF%E5%87%BA-d366999f29ff">BigQuery 資料的匯入匯出</a></li><li><a href="https://medium.com/@peilee_98185/bigquery-%E7%9A%84%E5%B7%A2%E7%8B%80%E7%B5%90%E6%A7%8B-array-struct-2f1c9bc1999">BigQuery 的巢狀結構 Array / Struct</a></li><li><a href="https://medium.com/@peilee_98185/bigquery-standard-sql-%E8%AA%9E%E6%B3%95-6afc1b0077ee">BigQuery Standard SQL 語法</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=135697d134ef" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery 資料的匯入匯出]]></title>
            <link>https://peilee-98185.medium.com/bigquery-%E8%B3%87%E6%96%99%E7%9A%84%E5%8C%AF%E5%85%A5%E5%8C%AF%E5%87%BA-d366999f29ff?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/d366999f29ff</guid>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Sat, 30 Mar 2019 15:45:03 GMT</pubDate>
            <atom:updated>2019-04-02T01:15:17.764Z</atom:updated>
            <content:encoded><![CDATA[<p>BigQuery 是 Google Cloud Platform 中，擔任 Data Warehouse 要角的關聯式資料庫。特色是高效能的運算速度，將傳統 Row Based 的關聯式資料庫，改為 Columns Based，適合用來做 Data Warehouse，不適合用來做需要高頻度新刪修如 Transaction 等的資料庫。使用語法的是 Legacy SQL 以及 Standard SQL。</p><h4>能匯入的資料源：</h4><p>1. 本機檔案</p><p>2. Google Cloud Storage</p><p>3. Google Sheets ( Google Drive )</p><p>4. BigQuery Table</p><h4>能匯出的資料格式：</h4><p>1. CSV (Google Drive 或本機)</p><p>2. JSON (Google Drive 或本機)</p><p>3. BigQuery Table</p><p>4. Google Sheets</p><p>BONUS. Data Studio</p><p>上述的選項中，只有 Google Sheets 的操作比較不那麼直覺一點，而且跟其他資料源有一些功能上的差異。另外，Data Studio 雖然不能算是匯出的資料格式，但想順帶一提一下，因此下面只介紹這部分的操作。</p><h4>匯入 Google Sheets 資料的方式</h4><ol><li>取得要匯入的 Google Sheet 的分享連結，大概會長這樣：</li></ol><pre><a href="https://docs.google.com/spreadsheets/d/[">https://docs.google.com/spreadsheets/d/</a><strong>[Spreadsheet ID]</strong>/edit?usp=sharing</pre><p>2. 轉成 Google Drive 的分享連結：</p><pre>https://drive.google.com/open?id=<strong>[Spreadsheet ID]</strong></pre><p>3. 到 BigQuery Create Table 選擇 Drive 並輸入 Google Drive 的分享連結，選擇 Google Sheet：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c5Ye9QAvxZp0OuICUnVcyg.png" /></figure><p>加上 Table Name 然後讓 BigQuery Auto Detect Schema 就可以點 Create Table了。之後只要在 Google Sheets 上做任何新刪修，都能直接同步在 BigQuery 上。</p><p>4. 限制：由於是連外部資料源非原生的 BigQuery Table，因此有以下限制</p><ul><li>不能保證資料的一致性（如果在 Query 的途中，資料有任何新刪修的話）。</li><li>無法利用到 BigQuery 的效能。</li><li>無法 Preview 資料，因為 BigQuery 不會儲存外部資料源的 Metadata。</li></ul><h4>匯出成 Google Sheets 的方式</h4><p>在下完 Query 之後的 Query Result 旁有一個 SAVE RESULTS 的按鈕，選擇 Google Sheets 就可以了：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/668/1*wUHjcd2jd6oDVNjDaOZC9g.png" /></figure><h4>視覺化</h4><p>剛才 SAVE RESULTS 旁邊有一個 EXPLORE IN DATA STUDIO，點進去就可以直接跳出 Data Studio 的視窗，直接連結 BigQuery 的資料做視覺化，簡單地拖拉點選之後，就能做出像是這樣看每個商品的訂單數及庫存的互動式圖表：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lksT83S0DYYX-weV0INt7Q.png" /><figcaption>資料來源是 Google 提供的範例資料</figcaption></figure><p>以上。</p><p>看其他我寫的 BigQuery 文章：</p><ul><li><a href="https://medium.com/@peilee_98185/bigquery-%E7%9A%84%E5%B7%A2%E7%8B%80%E7%B5%90%E6%A7%8B-array-struct-2f1c9bc1999">BigQuery 的巢狀結構 Array / Struct</a></li><li><a href="https://medium.com/@peilee_98185/bigquery-standard-sql-%E8%AA%9E%E6%B3%95-6afc1b0077ee">BigQuery Standard SQL 語法</a></li><li><a href="https://medium.com/@peilee_98185/bigquery-ml-%E5%88%86%E9%A1%9E%E6%A8%A1%E5%9E%8B-135697d134ef">BigQuery ML 分類模型</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d366999f29ff" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery Standard SQL 語法]]></title>
            <link>https://peilee-98185.medium.com/bigquery-standard-sql-%E8%AA%9E%E6%B3%95-6afc1b0077ee?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/6afc1b0077ee</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Sat, 30 Mar 2019 15:43:52 GMT</pubDate>
            <atom:updated>2019-04-02T01:14:52.632Z</atom:updated>
            <content:encoded><![CDATA[<p>最完整詳細的指令還是要看<a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/">官方文件</a>，這邊僅紀錄做 QwikLabs 時邊做的筆記。</p><h3>新增刪除</h3><h4>1. 新增 table</h4><ul><li>空的 table</li></ul><pre>#standardSQL <br>CREATE OR REPLACE TABLE <strong>[Dataset Name].[Table Name]</strong><br>#schema <br>(   <br><strong>[Column1]</strong> STRING OPTIONS(description=&quot;Unique ID&quot;),   <br><strong>[Column2]</strong> STRING OPTIONS(description=&quot;...&quot;) <br>)</pre><ul><li>從現有 table 中新增 table</li></ul><pre>#standardSQL <br>CREATE OR REPLACE TABLE <strong>[Dataset Name].[Table Name 2]</strong><br>#schema <br>(   <br><strong>[Column1]</strong> STRING OPTIONS(description=&quot;Unique ID&quot;),   <br><strong>[Column2]</strong> STRING OPTIONS(description=&quot;...&quot;) <br>)  <br>OPTIONS(    <br>    description=&quot;Data for 2017/01/01&quot;  <br>    ) <br>AS  <br>SELECT <strong>[Column1]</strong>, <strong>[Column2]</strong><br>FROM `<strong>[Project Name].[Dataset Name].[Table Name 1]</strong>`  <br>WHERE date = &#39;20170101&#39;;</pre><ul><li>新增以 date 切 partition 的 table：</li></ul><p>在沒有切 partition 的狀況下，即便沒有符合 WHERE 條件中的日期導致搜尋結果回傳 0 筆，還是必須掃描過整張 table，因而增加不必要的成本，並且耗時。切了 partition 之後，執行時會直接先去 partition 中確認有沒有這筆日期的資料，沒有的話就不會再繼續執行下去，能夠有效<strong>節省成本並提高效能</strong>。</p><pre>#standardSQL  <br>CREATE OR REPLACE TABLE <strong>[Dataset Name].[Table Name 2]</strong>  <br>PARTITION BY date_formatted  <br>OPTIONS(    description=&quot;a table partitioned by date&quot;  ) <br>AS  <br>SELECT <br>DISTINCT  <br>PARSE_DATE(&quot;%Y%m%d&quot;, date) AS date_formatted,  <br>fullvisitorId  <br>FROM `<strong>[Project Name].[Dataset Name].[Table Name 1]</strong>`</pre><p>PARSE_DATE()：將 STRING 轉成 DATE 型態</p><ul><li>新增自動到期地切 partition 的 table：如果基於資料隱私權或是為了避免儲存不必要的資料，可以加入 partition 的到期日，這樣一旦用不到的時候，partition 也會自動消失。</li></ul><pre>#standardSQL  <br>CREATE OR REPLACE TABLE <strong>[Dataset Name].[Table Name 2]</strong>  <br>PARTITION BY date  <br>OPTIONS (    <br>    partition_expiration_days=60,    <br>    description=&quot;weather precipitation, partitioned by day&quot;  <br>    ) <br>AS  <br>SELECT    <br>DATE(CAST(year AS INT64), <br>     CAST(mo AS INT64), <br>     CAST(da AS INT64)) AS date,      <br>prcp  <br>FROM `<strong>[Project Name].[Dataset Name].[Table Name 1]</strong>`</pre><p>確認一下</p><pre>#standardSQL <br>SELECT   <br>AVG(prcp) AS average,   <br>date,   <br>CURRENT_DATE() AS today,   <br>DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age,   <br>FROM `<strong>[Project Name].[Dataset Name].[Table Name 1]</strong>`<br>GROUP BY date, today, partition_age <br>ORDER BY partition_age DESC</pre><p>DATE_DIFF()：相減兩個 DATE 的 function。</p><h4>2. 新增單筆資料</h4><pre>INSERT INTO <strong>[Table Name]</strong> (<strong>[Column1], [Column2], ...</strong>) <br>VALUES ( <strong>[Value1], [Value2], ...</strong>);</pre><h4>3. 刪除</h4><pre>DELETE FROM <strong>[Table Name]</strong> WHERE <strong>[Conditions]</strong>;</pre><h3>想要定期更新某個 Query 的執行結果</h3><ol><li>可以用 <a href="https://cloud.google.com/bigquery/docs/scheduling-queries">BigQuery scheduled queries</a>or with a <a href="https://cloud.google.com/dataprep/">Cloud Dataprep</a> / <a href="https://cloud.google.com/dataflow/">Cloud Dataflow</a> workflow.</li><li>建立 View：習慣上 view 的命名會前綴 vw_ 或是後綴 _vw、_view</li></ol><pre>#standardSQL <br>CREATE OR REPLACE VIEW <strong>[Dataset Name].[View Name]</strong><br># 或是 CREATE VIEW IF NOT EXISTS 視需求而定<br>OPTIONS(   <br>    description=&quot;......&quot;,<br>    # 取得最近 90 天內的資料  <br>    expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), <br>                                                    INTERVAL 90 DAY) <br>    )<br>AS <br>SELECT *<br>FROM `<strong>[Project Name].[Dataset Name].[Table Name 1]</strong>`  <br>WHERE <strong>[Column]</strong> IS NOT NULL  <br>ORDER BY date DESC # latest transactions  <br>LIMIT 100;</pre><h3>好用的 function</h3><h4>1. STRING_AGG()</h4><p>取得並攤平每個訪客買的前十個產品名稱（以字母順序排列，逗點隔開）</p><pre>#standardSQL <br>CREATE OR REPLACE VIEW <strong>[Dataset Name].[View Name]</strong> <br>AS <br>SELECT DISTINCT date,<br>       visitId,<br>       STRING_AGG(<br>           DISTINCT productName ORDER BY productName LIMIT 10<br>       ) AS products_ordered  <br>FROM `<strong>[Project Name].[Dataset Name].[Table Name]</strong>`   <br>GROUP BY 1, 2  <br>ORDER BY date DESC # latest transactions    <br>LIMIT 10</pre><h4>2. SAFE_DIVIDE()</h4><p>避免分母為 0 噴錯的情況。</p><h3>合併 table</h3><h4>1. Column 的合併：JOIN</h4><ul><li>Cross Join：不管 Key，直接把 table2 的每一筆資料 join 到 table1 的每一筆資料。如果 table1 有 m 筆資料 table2 有 n 筆資料，join 完後會有 m X n 筆。</li><li>Inner Join：僅保留 table1 跟 table2 共同有的 Key 的資料。</li><li>Left Join： 僅保留 table1 有的 Key。</li><li>Right Join：僅保留 table2 有的 Key。</li><li>Full Join：同時保留 table1 跟 table2 有的 Key。</li></ul><h4>2. Row 的合併：UNION</h4><p>聯集</p><pre><strong>[Query 1]</strong><br>UNION<br><strong>[Query 2]</strong></pre><p>或是</p><pre><strong>[Query 1]</strong><br>UNION ALL<br><strong>[Query 2]</strong></pre><p>兩者的差別在於前者<strong>不包含重複的資料</strong>，後者包含。</p><p>合併的方式視需求而定，有些情況用萬用字元會更方便，假設想要查詢所有 2017 年的資料：</p><pre>#standardSQL <br>SELECT * FROM `ecommerce.sales_2017*`</pre><h3>常用情境：確認欄位的對應狀況並刪除重複值</h3><p>以產品 SKU 及產品名稱舉例：</p><p>首先看 1 個產品名稱是否對應到 N 個產品 SKU</p><pre>#standardSQL<br>SELECT<br>DISTINCT<br>COUNT(DISTINCT productSKU) AS SKU_count,<br>STRING_AGG(DISTINCT productSKU LIMIT 5) AS SKU,<br>productName<br>FROM `<strong>[Project Name].[Dataset Name].[Table Name]</strong>`<br>WHERE productSKU IS NOT NULL<br>GROUP BY productName<br>HAVING SKU_count &gt; 1<br>ORDER BY SKU_count DESC</pre><p>1 個產品名稱有 N 個 SKU 可能是因為同產品但不同的顏色、尺寸等等。</p><p>再看1 個產品 SKU 是否對應到 N 個產品名稱</p><pre>#standardSQL  <br>SELECT <br>DISTINCT <br>COUNT(DISTINCT productName) AS product_count, <br>STRING_AGG(DISTINCT productName LIMIT 5) AS product_name, <br>productSKU <br>FROM `<strong>[Project Name].[Dataset Name].[Table Name]</strong>` <br>WHERE productName IS NOT NULL <br>GROUP BY productSKU <br>HAVING product_count &gt; 1 <br>ORDER BY product_count DESC</pre><p>1 個產品 SKU 有 N 個產品名稱，可能是因為人為不同的取名習慣造成的，因此要刪除重複的產品名稱：</p><pre>#standardSQL<br># 取得每個產品 SKU 以及對應的產品名稱<br>WITH product_query AS (<br>  SELECT<br>  DISTINCT<br>  productName,<br>  productSKU<br>  FROM `<strong>[Project Name].[Dataset Name].[Table Name]</strong>`<br>  WHERE productName IS NOT NULL<br>)</pre><pre>SELECT k.* FROM (<br>  SELECT ARRAY_AGG(x LIMIT 1)[OFFSET(0)] k<br>  FROM product_query x<br>  GROUP BY productSKU <br>);</pre><ul><li>ARRAY_AGG(x LIMIT 1)：從 product_query 的 1 個 SKU 對應到 N 個產品名稱的資料中，<strong>只取 1 個</strong>產品名稱。</li><li>[OFFSET(0)]：<strong>取第 1 個</strong>產品名稱，OFFSET 指的是位置。</li></ul><p>以上。</p><p>看其他我寫的 BigQuery 文章：</p><ul><li><a href="https://medium.com/@peilee_98185/bigquery-%E8%B3%87%E6%96%99%E7%9A%84%E5%8C%AF%E5%85%A5%E5%8C%AF%E5%87%BA-d366999f29ff">BigQuery 資料的匯入匯出</a></li><li><a href="https://medium.com/@peilee_98185/bigquery-%E7%9A%84%E5%B7%A2%E7%8B%80%E7%B5%90%E6%A7%8B-array-struct-2f1c9bc1999">BigQuery 的巢狀結構 Array / Struct</a></li><li><a href="https://medium.com/@peilee_98185/bigquery-ml-%E5%88%86%E9%A1%9E%E6%A8%A1%E5%9E%8B-135697d134ef">BigQuery ML 分類模型</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6afc1b0077ee" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery 的巢狀結構 Array / Struct]]></title>
            <link>https://peilee-98185.medium.com/bigquery-%E7%9A%84%E5%B7%A2%E7%8B%80%E7%B5%90%E6%A7%8B-array-struct-2f1c9bc1999?source=rss-6a3cf9d12806------2</link>
            <guid isPermaLink="false">https://medium.com/p/2f1c9bc1999</guid>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Pei Lee]]></dc:creator>
            <pubDate>Sat, 30 Mar 2019 15:42:38 GMT</pubDate>
            <atom:updated>2019-04-02T01:14:16.179Z</atom:updated>
            <content:encoded><![CDATA[<p>一般的資料庫中，我們會看到這樣一個欄位一個值：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s8Y3bxAmzA13u7n696hxLg.png" /><figcaption>圖片來源：QwikLabs</figcaption></figure><p>如果今天想要紀錄誰吃了哪些水果，則會變成：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qDDgWuOYiOKsln6hjXBfww.png" /><figcaption>圖片來源：QwikLabs</figcaption></figure><p>這樣便不符合資料庫正規化的原則，因為 name 一直重複地出現。在傳統做法中，我們會很直接地想到拆成兩張表，但在 BigQuery 中有個更快速的方法處理這個問題，用 Array 的方式儲存達到下面這種效果：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ebHBoAEdwpLg2T-K-wjqLQ.png" /><figcaption>圖片來源：QwikLabs</figcaption></figure><p>雖然看起來跟原本那剛表很像，但它實際上只算 2 個 Row。它的概念比較像是這樣的狀況：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uycyjdmTrKAPYbr7iGFfnw.png" /></figure><h3>Array</h3><p>嘗試看看這樣的 Query：</p><pre>#standardSQL<br>SELECT [&#39;raspberry&#39;, &#39;blackberry&#39;, &#39;strawberry&#39;, &#39;cherry&#39;] AS fruit_array</pre><p>得到的結果會像是這樣 1 個 Row：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/222/1*bn-t-6YHjBS0-nWTAKPVUw.png" /></figure><p>如果要把它變成多個 Row 則要使用 UNNEST()：</p><pre>#standardSQL<br>SELECT * <br>FROM UNNEST([&#39;raspberry&#39;, &#39;blackberry&#39;, &#39;strawberry&#39;, &#39;cherry&#39;])</pre><p>UNNEST()：把 Array 轉成 Table，所以 UNNEST() 是放在 FROM 後面的。</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/212/1*qqjHafZWqS75LOLttXjcRA.png" /></figure><p>然而，Array 裡面每個元素都<strong>必須是相同的資料型態</strong>。如果需要儲存不同的資料型態，則需要使用 STRUCT ()。</p><h3>STRUCT</h3><p>試試這樣的 Query：</p><pre><em>#standardSQL</em> <br>SELECT STRUCT(&quot;Rudisha&quot; as name, 23.4 as split) as runner</pre><p>可以同時存在 STRING 以及 FLOAT：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jm1l9dCjRXiR039p0UycwQ.png" /></figure><p>另外，也可以放入 Array：</p><pre><em>#standardSQL</em> <br>SELECT STRUCT(&quot;Rudisha&quot; as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PO9hIDQ49IMCHn57_8KVxQ.png" /></figure><h3>如何知道一個欄位是 STRUCT 還是 ARRAY？</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7Kmx3T2k5Ssbd7b7QNaUvA.png" /></figure><p>STRUCT：Type =RECORD, Mode = REPEATED，能容納不同型態。</p><p>ARRAY：Mode = REPEATED，只能容納單一型態如上圖的 FLOAT。</p><h3>如何 Query 這樣的 Table？</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/628/1*zJBqU2zsvOCf9qOmT8v9iQ.png" /><figcaption>table name: race_results</figcaption></figure><p>如何下 Query 執行出這樣的結果：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/868/1*dbLdEzfZiW4YYdjobgEcFQ.png" /></figure><p>先想像假設今天是傳統的資料庫，我們可能會去 JOIN race 這張表以及 participants 這張表，今天不一樣的只是 participants 是一個 STRUCT 的物件，但我們仍可以把 STRUCT 視為 Table 中的 Table，因此可以用 CROSS JOIN：</p><pre><em>#standardSQL</em> <br>SELECT race, participants.name <br>FROM racing.race_results <br>CROSS JOIN race_results.participants </pre><p>注意 participants 還是隸屬於 race_results 這張表下的，因此要將 table name 也打上去。有個更簡潔的寫法：</p><pre><em>#standardSQL</em> <br>SELECT race, participants.name <br>FROM racing.race_results AS r, r.participants</pre><p>用「,」代替 CROSS JOIN。</p><p>不論是 Array 或是 Struct，在進行操作前一定要先攤平裡面的元素。</p><h4>如何找到名字是 R 開頭的選手，並將總成績由好到壞排序？</h4><pre>#standardSQL<br>SELECT<br>  p.name,<br>  SUM(split_times) as total_race_time<br>FROM racing.race_results AS r<br>, UNNEST(r.participants) AS p<br>, UNNEST(p.splits) AS split_times<br>WHERE p.name LIKE &quot;R%&quot;<br>GROUP BY p.name<br>ORDER BY total_race_time;</pre><p>實際測過，發現有沒有加 UNNEST 結果都一樣。</p><p>以上。</p><p>看其他我寫的 BigQuery 文章：</p><ul><li><a href="https://medium.com/@peilee_98185/bigquery-%E8%B3%87%E6%96%99%E7%9A%84%E5%8C%AF%E5%85%A5%E5%8C%AF%E5%87%BA-d366999f29ff">BigQuery 資料的匯入匯出</a></li><li><a href="https://medium.com/@peilee_98185/bigquery-standard-sql-%E8%AA%9E%E6%B3%95-6afc1b0077ee">BigQuery Standard SQL 語法</a></li><li><a href="https://medium.com/@peilee_98185/bigquery-ml-%E5%88%86%E9%A1%9E%E6%A8%A1%E5%9E%8B-135697d134ef">BigQuery ML 分類模型</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2f1c9bc1999" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>