黑皮考町

前言 與傳統的關聯式資料庫不同, BigQuery 並不支援使用 ALTER 來修改資料表內的欄位, 如果很不幸的真的需要改欄位, 我們只能重新建立新的資料表 CREATE OR REPLACE TABLE ` dataset.usr_new ` AS SELECT * EXCEPT (usrType), CAST(usrType AS INT64) AS usrType, FROM `dataset.usr`; 分區表 (Partitioned Table) 以上的方法只適合用在普通的資料表, 若想要修改分區表內的欄位資訊, 建議使用 bq query 的指令來做 bq query 支援將查詢的結果寫入到指定的資料表中, 除此之外還可以用查詢結果來建立分區表(如下) bq --location=location query \ --destination_table project_id:dataset.table \ --time_partitioning_field column \ --use_legacy_sql=false \ 'query' 分區表都會有兩個虛擬的欄位 _PARTITIONDATA, _PARTITIONTIME 用來記錄資料的時間,  這邊需要注意的是 bq query 沒辦法將舊資料表內的虛擬欄位的資訊直接寫到新的資料表中,  若想將這些資訊保留下來, 必須額外建立新的欄位來放這些資訊 SELECT * EXCEPT (usrType), CAST(usrType AS INT64) AS usrType, _PARTITIONTIME AS createdAt FROM ` dataset.usr `; 完整的指令 bq query \ --destination_table 'dataset.usr_new' \ --time_partitioning_field createdAt \ --use_legacy_sql=false --append_table \ ' SELECT * EXCEPT (usrType), CAST(usrType AS INT64) ...

前言 傳統的 Relational database 可以使用 SQL 語言以 GROUP BY 的方式將資料分群然後做計算 SELECT country, COUNT(*) AS Count FROM `bi_dataset.usr` GROUP BY country DESC ORDER BY 1 若想要在 Elastic Search  上實現相同的結果的話, 可以使用 Terms Aggregation { "size": 0, "aggs" : { "country" : { "terms" : { "field" : "country" } } } } 但實務上基於效能的考量, 通常不會希望回傳全部的結果, 而是以分頁的方式傳回部分資料 SQL的做法是在語法裡加入 LIMIT 以及 OFFSET 來控制結果 SELECT country, COUNT(*) AS count FROM `dataset.usr` GROUP BY country ORDER BY 1 DESC LIMIT 20 /* 限制資料量 */ OFFSET 100 /* 取得某特定區段的資料, 即 skip 前100筆 */ 而在 Elastic Search 裡, 可以藉由 bucket sort aggregation 來實現, 分頁的部分可以透過 size, 以及from 來控制 { "size": 0, "aggs": { "country": { "aggs": { "sort_country": { "bucket_sort": { "sort": [ { "_count": { ...

前言 近年來, 商業智慧( Business Intelligence, BI )的運用已漸漸地受到企業的重視, 決策者可以藉由 BI 提供的資料來做業務的分析與預測 在做業務分析的時候, 通常會以每日活躍使用者數( Daily Active User, DAU ), 每週活躍使用者數( Weekly Active User, WAU ), 以及每月活躍使用者數( Monthly Active User, MAU )的多寡, 來暸解目前產品的營運狀況 要計算出這些資訊其實並不難, 首先以 Group By "日期"的方式算就可以先算出 DAU WITH DAUtbl AS ( SELECT _PARTITIONDATE AS date, COUNT(*) as dau FROM my_dataset.usr WHERE _PARTITIONDATE BETWEEN DATE_SUB(DATE('2020-03-16T13:15:30Z'), INTERVAL 7 - 1 DAY) AND DATE('2020-03-21T13:15:30Z') GROUP BY _PARTITIONDATE ) 有了每日人數( DAU )之後, 接下來就是來算 WAU 計算 WAU 時, 須將當日以及前六天的 Active User 數量都加總起來 而想要取得七天內的資料, 可以先透過 CROSS JOIN 的方式將每一日的資料都複製成七份 SELECT * FROM DAUtbl, UNNEST(GENERATE_ARRAY(0, 7 - 1 )) i 每個副本都會有個索引值 i (如下) 有了七份的副本之後, 再將資料移動到合適的群組(日期)做加總, 最後就可以獲得每一日 WAU 的數目 SELECT DATE_ADD(date, INTERVAL i DAY) date_grp <--- 移動資料 , SUM(DISTINCT IF( i < 7 ,dau, null)) wau FROM DAUtbl, UNNEST(GENERATE_ARRAY(0, 7 - 1 )) i GROUP BY 1 ORDER BY...

Inner Join  就是合併兩張表中有交集的資料 Left Join  也是在合併兩張表,但不同於Inner Join的是, 若在右方表格找不到對應於左方表格的資料時, 預設會給null代替 在Entity Framework中如何實現Inner Join from c in Students join o in Addresses on c.StudentId equals o.StudentId select new { c.StudentName , o.AddressName} 對應的T-SQL SELECT [t1].[StudentName], [t0].[AddressName] FROM [Students] AS [t0] INNER JOIN [Addresses] AS [t1] ON [t1].[StudentId] = [t0].[StudentId] 在Entity Framework中如何實現Left Join from c in Categories join o in Products on c.CategoryID equals o.CategoryID  as ps from p in ps.DefaultIfEmpty() select new { c.CategoryName ,p.ProductID, p.ProductName } ps.DefaultIfEmpty 表示join後的結果會存在ps, 若在Products中找不到對應資料就會回傳null 對應的T-SQL SELECT [t0].[Categories], [t1].[Products] FROM [Categories] AS [t0] LEFT OUTER JOIN [Products] AS [t1] ON [t1]. CategoryID = [t0].[CategoryID]

前言 在擬定商業策略的時候 Cohorts Retention 一直是很常被用來參考的指標, 透過這個指標可以了解客戶對於產品的黏著度倒底有多高, 進而去調整產品規劃或是公司的營運方向 Cohorts Retention Rate 又稱回客率,  也就是當過了一段時間之後仍還繼續使用產品的客戶比率, 若以七天為一個週期來看的話, 就是每週的回客率 每週回客率 ＝ 當週的舊客戶數/第0週的所有客戶數 舉例來說, 以下是一個週回客率的表格, 總共分析六週(Week 0 ~ Week 5), 而在Week 0 出現的客戶群即在左方日期區間內出現的客戶群, 而紅色箭頭的 25.7% 指的是出現在Week 0(Feb 3 - Feb 9) 的客戶仍然有出現在Week 1的(即當週的舊客戶)占所有出現在 Week 0 的客戶的比例 SQL 計算 Retention 如下圖所示, Retention 計算出來之後會呈現出一個左上三角形的圖表 而這整張表的計算其實並不複雜, 基本上只要知道第一列怎麼算, 剩下的也都不是問題, 只要如法炮製就好 接下來的演練將使用 Open Data 的資料 Step 1. 首先算出第0週的所有使用者 SELECT DISTINCT user_pseudo_id FROM `firebase-public-project.analytics_153293282.events_*` WHERE _TABLE_SUFFIX BETWEEN '20180731' AND '20180807' Step 2. 計算第一週(Week 1)還留存多少客戶 這個部分就是將第0週的客戶資料跟第1週的直接做JOIN, 就可得知在第一週時有哪些第0週的客戶回流了, 如下 WITH week_0 AS( SELECT DISTINCT user_pseudo_id FROM `firebase-public-project.analytics_153293282.events_*` WHERE _TABLE_SUFFIX BETWEEN '20180731' AND '20180807' ) SELECT ...

前言 相信.NET的開發者對Entity Framework(簡稱EF)絕對不陌生, 這是基於.NET的ORM框架, 除了可以讓開發者用存取物件的方式與資料庫溝通之外, 還可以搭配LINQ to Entity的技術, 更輕易地撰寫查詢邏輯, EF底層會自動將這些邏輯轉換為對應的SQL語法,然後執行在SQL Server上 雖然LINQ to Entity很方便, 但在使用上也必須小心, 不然有可能在編譯的時候都OK, 但Runtime時卻跳出以下的錯誤訊息 錯誤訊息 LINQ to Entities does not recognize the method System.String Format 錯誤的使用方式 這個問題出在於Select 的時侯呼叫了 string . Format 　 在這個情況下使用LINQ to Entities時, C#會告訴SQL Server去執行 string . Format , 但由於SQL Server上沒有相對應的指令, 所以才會出現這個錯誤訊息 解決方法 可以將Query分成兩段, 前半段最後呼叫 .AsEnumerable() 將資料讀到記憶體中, 後半段Query就能以LINQ to Objects的方式來操作資料, string . Format 也可以正常的被呼叫

前言： 簡單的說Store Procedure 可以讓我們預先寫好一段SQL語法, 等要用的時候再去呼叫他 EXEC     @return_value = [dbo].[GetImgByProject] 這麼做的好處是我們可以讓複雜的query便精簡, 除此之外由於Store Procedure是已經編譯完後且最佳化完後的產物, 所以執行Store Procedure也可以提升query的整體效率 以下是以MS SQL示範如何建立使用Store Procedure 環境: MS SQL 建立Store Procedure Step 1. 編寫建立語法 起手式 Create Procedure  YOURNAME AS YOURQUERY 列如: Create Procedure GetImgByProject AS SELECT TOP 1000 [SID] ,[Name] ,[PartNumber] ,[Brand] ,[ProjectName] ,[CreateDatetime] ,[UpdateDatetime] FROM [ DATA . MDF ].[dbo].[modelname] 如果需要帶參數進來, 可以在Create Procedure後加入參數名, 型別, 長度 @PARAM   varchar (50)  列如: Create Procedure GetImgByProject @ProjectName varchar (50) AS SELECT TOP 1000 [SID] ,[Name] ,[PartNumber] ,[Brand] ,[ProjectName] ,[CreateDatetime] ,[UpdateDatetime] FROM [ DATA . MDF ].[dbo].[modelname] WHERE ProjectName = @ProjectName Step 2. 執行Execute建立 Store P...

語法MySQL轉 MS SQL 最近在嘗試將MySQL的資料轉放到MS SQL上 由於部分語法不一樣 所以在import時要先做個轉換 以下的連結非常有幫助 http://www.sqlines.com/online

當你在SSMS上嘗試修改資料庫時（如更改名字） 若此時有其他程式正在query這個資料庫的話 我們就有可能會遇到以下的錯誤訊息 Msg 5030, Level 16, State 2, Line 1 The database could not be exclusively locked to perform the operation. 解決方法是: 將資料庫轉為 Single user的模式 再來修改資料庫 use  master ALTER   DATABASE  BOSEVIKRAM  SET  SINGLE_USER  WITH   ROLLBACK  IMMEDIATE    ALTER   DATABASE  BOSEVIKRAM MODIFY NAME  =   [ BOSEVIKRAM_Deleted ] ALTER   DATABASE  BOSEVIKRAM_Deleted  SET  MULTI_USER http://wiki.lessthandot.com/index.php/Kill_All_Active_Connections_To_A_Database http://blog.miniasp.com/post/2009/05/15/Using-SINGLE_USER-mode-to-change-Database-configuration.aspx

如何讓LINQ查詢的結果輸出table裡所有的欄位資料 今天假如我有一個 Table employee 在Table 裡有 ID , name, phone  三個欄位 若我想查詢所有名字出現"Andy"的員工時 使用一般的SQL查詢的語法如下: Select * from  employee where name like 'Andy'  那如果是用LINQ來查詢呢！？ 答案: var profile = ( from p in employee                         where p.name.Contains( "Andy")                         select p )                       . toList();

Make a query with MySQL that check if a string in certain column contains a specified substring('Success' ) Two ways: use function INSTR() to look up WHERE  INSTR(`Description`, 'Success') > 0 Use Like operator with % character WHERE  `Description` LIKE ' % Success % '