仕事の後でやっと知った4つのSQLの密技

SQLはビッグデータ業者の必須スキルであり、ほとんどのビッグデータ技術フレームワークもSQLのソリューションを提供しています.SQLは長い間衰えず、長い間新しいプログラミング言語と言える.特に数倉分野ではSQLを使うのが日常茶飯事です.この文書では、面接と仕事でよく使われる4つのテクニックを共有します.具体的には、次のとおりです.

日付と期間の使用

テンポラリ・テーブルとCommon Table Expression(WITH)

AggregationとCASE WHENの併用

Window Functionのその他の用途

数倉?SQLを書くのではないでしょうか...
第一:日付と期間の使用
日付と時間帯のフィルタリングは、レポート、ダッシュボード、各種分析を引き出す際に、週、月、四半期、年度の表現が分析に考慮すべき重点であることが多いため、仕事でよく使われています.
タイムセグメントの抽出:Extract

構文

-- field   day、hour、minute, month, quarter  
-- source   date、timestamp  
extract(field FROM source)

を使用

SELECT extract(year FROM '2020-08-05 09:30:08');   --     2020
SELECT extract(quarter FROM '2020-08-05 09:30:08');   --     3
SELECT extract(month FROM '2020-08-05 09:30:08');   --     8
SELECT extract(week FROM '2020-08-05 09:30:08');   --     31,       
SELECT extract(day FROM '2020-08-05 09:30:08');  --     5
SELECT extract(hour FROM '2020-08-05 09:30:08');   --     9
SELECT extract(minute FROM '2020-08-05 09:30:08');   --     30
SELECT extract(second FROM '2020-08-05 09:30:08');   --     8

抽出可能なフィールドは、データベースによって若干異なります.Hiveを例にとると、day, dayofweek, hour, minute, month, quarter, second, week yearがサポートされている.その中で、周、月、年が最も広く使われています.社内製品でも、商用製品でも提供されているデータのバックグラウンド統計でも、周報と月報(例えば7日近く、30日近く)が最も表現の周期を重視しているからです.
注意:
impalaサポート:YEAR,QUARTER,MONTH,DAY,HOUR,MINUTE,SECOND,MILLISECOND,EPOCH
Hiveサポート:day,dayofweek,hour,minute,month,quarter,second,week,year
HiveはHive 2.2.0バージョンからこの関数を導入しました
週の抽出

構文

週の区間で集計する場合は、月曜日の日付と日曜日の日付を識別する必要があります.この場合、次の関数がよく使用されます.

next_day(STRING start_date, STRING day_of_week)
--                    
-- 2020-08-05   
SELECT next_day('2020-08-05','MO') --           ：2020-08-10
SELECT next_day('2020-08-05','TU') --           ：2020-08-11
SELECT next_day('2020-08-05','WE') --           ：2020-08-12
SELECT next_day('2020-08-05','TH') --           ：2020-08-06，     
SELECT next_day('2020-08-05','FR') --           ：2020-08-07，     
SELECT next_day('2020-08-05','SA') --           ：2020-08-08，     
SELECT next_day('2020-08-05','SU') --           ：2020-08-09，     
--           （Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）

を使用
では、現在の日付がある週の月曜日に対応する日付をどのように取得すればいいのでしょうか.現在の日付の来週1日に対応する日付を取得し、7日減算するだけで取得できます.

SELECT date_add(next_day('2020-08-05','MO'),-7);

同じように、現在の日付が存在する週の日曜日に対応する日付を取得するには、現在の日付の来週の日付を取得してから1日減算するだけで、次のようになります.

select date_add(next_day('2020-08-05','MO'),-1) 
-- 2020-08-09

月の抽出

構文

月を単一の日付から抽出する方法については、LAST_DAYという関数は、毎月の日付をその月の最終日(28日、29日、30日または31日)に変更できます.以下のようになります.

last_day(STRING date)

を使用

SELECT last_day('2020-08-05'); -- 2020-08-31

上記以外にもdate_を使用できますformat関数、たとえば:

SELECT date_format('2020-08-05','yyyy-MM');
-- 2020-08

日付の範囲
月のWindow:add_を使用monthsにtrunc()を加えた応用

--              
-- 2020-07-05
select add_months('2020-08-05', -1)

--            
-- 2020-08-01
select trunc("2020-08-05",'MM')

上記の例から分かるように、add_を単純に使用するmonthsは、Nヶ月を減らす使い方で、ちょうど整数月のデータを取ることができますが、trunc()関数を加えると、Nヶ月の1番から値を取り始めます.

--   2020-07-05 2020-08-05    
BETWEEN add_months('2020-08-05', -1) AND '2020-08-05' 
--   2020-07-01 2020-08-05      
BETWEEN add_months(trunc("2020-08-05",'MM'),-1) AND '2020-08-05' 

第二:仮表とCommon Table Expression(WITH)
この2つの方法は日常業務でよく用いられるものであり,いくつかの複雑な計算タスクでは,あまり多くのJOINを避けるために,通常,抽出する必要があるデータの一部をテンポラリテーブルやCTEの形式で主なクエリーブロックの前で抽出する.
テンポラリ・テーブルの作り方:

CREATE TEMPORARY TABLE table_1 AS  
    SELECT 
        columns
    FROM table A;
CREATE TEMPORARY table_2 AS 
    SELECT
        columns
    FROM table B;

SELECT
    table_1.columns,
    table_2.columns, 
    c.columns 
FROM table C JOIN table_1
     JOIN table_2;

CTEのやり方:

--   Hive、Impala      ，    MySQL   (     )
WITH employee_by_title_count AS (
    SELECT
        t.name as job_title
        , COUNT(e.id) as amount_of_employees
    FROM employees e
        JOIN job_titles t on e.job_title_id = t.id
    GROUP BY 1
),
salaries_by_title AS (
     SELECT
         name as job_title
         , salary
     FROM job_titles
)
SELECT *
FROM employee_by_title_count e
    JOIN salaries_by_title s ON s.job_title = e.job_title

TEMP TABLEとCTE WITHの使い方はよく似ていますが、Queryをより一目瞭然で優雅で簡潔にすることが目的です.多くの人はすべてのQueryを単一のブロックに書くことに慣れており、JOINやSUBQUERYを使いすぎて、最後に論理が失われ、自分でもどこまで書いているのか分からないため、TEMP TABLEやCTEを適時に補助として使うのは、絶対にプラスです.
第三:AggregationとCASE WHENの併用
Aggregation function(SUM/COUNT/COUNT DISTINCT/MIN/MAX)とCASE WHENを組み合わせるのが最強で最も興味深い使い方です.このような使用はEXCELのSUMIF/COUNTIFのような効果を生み出し,この方法で多くの効率的な分析を行うことができる.

Table Name: order

Column: register_date, order_date, user_id, country, order_sales, order_id

データの準備

CREATE TABLE order(
    register_date string,
    order_date string,
    user_id string,
    country string,
    order_sales decimal(10,2),
    order_id string);

INSERT INTO TABLE order VALUES("2020-06-07","2020-06-09","001",'c0',210,"o1");
INSERT INTO TABLE order VALUES("2020-06-08","2020-06-09","002",'c1',220,"o2");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-10","003",'c2',230,"o3");
INSERT INTO TABLE order VALUES("2020-06-09","2020-06-10","004",'c3',200,"o4");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-20","005",'c4',300,"o5");
INSERT INTO TABLE order VALUES("2020-06-10","2020-06-23","006",'c5',400,"o6");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-19","007",'c6',600,"o7");
INSERT INTO TABLE order VALUES("2020-06-12","2020-06-18","008",'c7',700,"o8");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-09","009",'c8',100,"o9");
INSERT INTO TABLE order VALUES("2020-06-15","2020-06-18","0010",'c9',200,"o10");
INSERT INTO TABLE order VALUES("2020-06-15","2020-06-19","0011",'c10',250,"o11");
INSERT INTO TABLE order VALUES("2020-06-12","2020-06-29","0012",'c11',270,"o12");
INSERT INTO TABLE order VALUES("2020-06-16","2020-06-19","0013",'c12',230,"o13");
INSERT INTO TABLE order VALUES("2020-06-17","2020-06-20","0014",'c13',290,"o14");
INSERT INTO TABLE order VALUES("2020-06-20","2020-06-29","0015",'c14',203,"o15");

CASE WHEN時間、留保率/使用率の分析を行う

--       
set hive.groupby.skewindata = false
--              
set hive.groupby.orderby.position.alias = true

SELECT
    date_add(Next_day(register_date, 'MO'),-1) AS week_end,
    COUNT(DISTINCT CASE WHEN order_date BETWEEN register_date AND date_add(register_date,6) THEN user_id END) AS first_week_order,
    COUNT(DISTINCT CASE WHEN order_date BETWEEN date_add(register_date ,7) AND date_add(register_date,13) THEN user_id END) AS sencod_week_order,
    COUNT(DISTINCT CASE WHEN order_date BETWEEN date_add(register_date ,14) AND date_add(register_date,20) THEN user_id END) as third_week_order
FROM order
GROUP BY 1

上記の例では、ユーザーが登録後に注文を作成する動作があるかどうかを示します.例えば登録後の1週目、2週目、3週目にそれぞれどれだけの注文ユーザーがいるかで、ユーザーの使用状況と保存状況を分析することができます.
注:上記の使用方法では、2つのパラメータを構成する必要があります.
hive.groupby.skewindata=false:複数の列の重複を許可します.そうしないと、エラーが発生します.SemanticException [Error 10022]: DISTINCT on different columns not supported with skew in data
hive.groupby.orderby.position.alias=true:位置番号を使用してグループ化またはソートを許可します.そうしないと、エラーが発生します.SemanticException [Error 10025]: line 79:13 Expression not in GROUP BY key ''MO''
CASE WHEN時間、ユーザーごとの消費金額の分析を行う

SELECT
    user_id,
    SUM (CASE WHEN order_date BETWEEN register_date AND date_add(register_date,6) THEN order_sales END) AS first_week_amount,
    SUM (CASE WHEN order_date BETWEEN date_add(register_date ,7) AND date_add(register_date,13) THEN order_sales END) AS second_week_amount
    FROM order
GROUP BY 1

登録と消費の日付を絞り込み,消費金額統計を行うことにより,各ユーザの各期間(登録後1週目,2週目…このように推す)の消費金額は,ユーザが消費習慣を維持し続けているか,あるいは消費金額が低くなっているかなどの分析が可能である.
CASE WHEN数量、消費金額が一定額を超える数量分析

SELECT
    user_id,
    COUNT(DISTINCT CASE WHEN order_sales >= 100 THEN order_id END) AS count_of_order_greateer_than_100
FROM order
GROUP BY 1

上記の例はcountifのような使い方で、各ユーザーに対して、ある値より大きい受注金額を統計し、価値の高い顧客を分析してフィルタリングします.
CASE WHEN数、時間を加えた使い方

SELECT
    user_id,
    MIN(CASE WHEN order_sales > 100 THEN order_date END) AS first_order_date_over1000,
    MAX(CASE WHEN order_sales > 100 THEN order_date END) AS recent_order_date_over100
FROM order
GROUP BY 1

CASE WHENにMIN/MAX時間を加えると、ユーザーが使用中に一定額を超える注文日を初めて購入し、最近一定額を超える注文日を購入したことがわかります.
第四:Window Functionのその他の用途
Window Functionは仕事でよく使う関数であり、面接でよく聞かれる質問でもあります.一般的な使用シーンは、topNをグループ化することです.本稿で紹介するもう一つの使い方は,開窓関数を用いてユーザアクセスsession解析を行う.
sessionとは、指定された期間内にユーザがウェブサイト上で発生する一連のインタラクションを指す.例えば、1回のセッションには、複数のウェブブラウズ、イベント、ソーシャル・インタラクション、および電子商取引が含まれることができる.sessionは、ユーザーがWebサイトで実行する操作を含むコンテナに相当します.
sessionには30分、すなわち非アクティブ状態が30分を超えると、そのsessionは時代遅れになります.
仮に張三がウェブサイトにアクセスしたとしたら、彼がウェブサイトに着いた瞬間から、時間を計り始めた.30分が過ぎても、張三が何の形式もやり取りしていない場合は、今回のセッションが終了したと見なされます.しかし、張三がある要素とインタラクティブ(例えば、あるイベントが発生したり、ソーシャルインタラクティブになったり、新しいページが開かれたり)すると、そのインタラクティブな時間に加えて30分増加し、期限切れをリセットします.
データの準備

Table Name: user_visit_action

Columns: user_id, session_id , page_url, action_time

CREATE TABLE user_visit_action( 
    user_id string,
    session_id string,
    page_url string,
    action_time string);

INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://a.com","2020-08-06 13:34:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://b.com","2020-08-06 13:35:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://c.com","2020-08-06 13:36:11.478");

INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://a.com","2020-08-06 14:30:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://b.com","2020-08-06 14:31:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://e.com","2020-08-06 14:33:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://f.com","2020-08-06 14:35:11.478");

INSERT INTO TABLE user_visit_action VALUES("002","ss003","http://u.com","2020-08-06 18:34:11.478");
INSERT INTO TABLE user_visit_action VALUES("002","ss003","http://k.com","2020-08-06 18:38:11.478");

ユーザーアクセスセッション分析
例の資料表には,上記のように,利用者,訪問者,ページの連結と時間がある.以下ではpartition byを用いて,各ユーザの異なる訪問間の閲覧行動を表す.

SELECT
    user_id,
    session_id,
    page_url,
    DENSE_RANK() OVER (PARTITION BY user_id, session_id ORDER BY action_time ASC) AS page_order,
    MIN(action_time) OVER (PARTITION BY user_id, session_id) AS session_start_time,
    MAX(action_time) OVER (PARTITION BY user_id, session_id) AS session_finisht_time
FROM user_visit_action

上記のクエリは、各ユーザに対して、毎回の訪問に対して、ページを閲覧する動作の前後順、およびセッションの開始と終了の時間を返し、これに基づいてTEMP TABLEまたはCTEにこの結果を格納し、さらに分析することができる.
小結
本文は主に4つの仕事と面接でよく出会うSQLの使用テクニックを共有しました.もちろん、これらは具体的な分析業務と密接に関係しています.最後に、あなたがSQL boy or SQL girlであろうと、いくつかのテクニックを身につけさえすれば、Happy SQL queryingができると信じています.