SQLクラシックインタビュー 10の質問

I.サマリー

データワーカーとして、SQLは日常業務で最も一般的に使用されるデータ抽出および単純な前処理言語です。その広範な使用と習得性は、プロダクトマネージャーや研究開発などの他の職種でも広く使用されているため、この記事では、古典的な面接問題を組み合わせて、データ開発面接を通じてSQL手法と実際の実践を紹介します。以下のトピックは、私の経験とオンラインで共有した中難易度のSQL問題から来ています。

2.テーマの考え方

• 単純-group by & limitのような使用法や、randクラスのようなあまり使われない関数を検討します。テーブル間の関連付けが含まれます。

• 中-ウィンドウ関数の基本的な使用法を検討します。テーブル間の関連付けがあり、トリッキーの代わりに自己関連付けがあります。

• 難易度-中央値またはより複雑な数値概念があり、特定の要件に従って列を生成する必要がある場合があります。一般的に、中間表はより明確になります。

3. SQLの本当の問題

最初のテーマ

• order注文テーブル、フィールドgoods_id， amount ;

• pvテーブルを参照します：goods_id，uid;

• 商品は総売上高でソートされ、トップ10、トップ10 ~トップ20、その他の3つのグループに分けられます。

各グループの閲覧ユーザー数を求める（同じグループ内の同一ユーザーは1回しかカウントできない）

create table if not exists test.nil_goods_category as
select goods_id
,case when nn<= 10 then 'top10'
      when nn<= 20 then 'top10~top20'
      else 'other' end as goods_group
from
(
    select goods_id
    ,row_number() over(partition by goods_id order by sale_sum desc) as nn
    from
    (
        select goods_id,sum(amount) as sale_sum
        from order
        group by 1
    ) aa
) bb;
select b.goods_group,count(distinct a.uid) as num
from pv a
left join test.nil_goods_category b
on a.goods_id = b.goods_id
group by 1;

第二のテーマ

商品活動表goods_event，g_id（重複する可能性あり），t 1（開始時刻），t 2（終了時刻）

時間帯（t3，t4）が与えられ，その時間帯に活動している商品数を求める.

1.

select count(distinct g_id) as event_goods_num
from goods_event
where (t1<=t4 and t1>=t3)
or (t2>=t3 and t2<=t4)

select count(distinct g_id) as event_goods_num
from goods_event
where (t1<=t4 and t1>=t3)
union all

第三の主題

商品アクティビティパイプラインテーブル、テーブル名はイベント、フィールド：goods_id， time;

イベント参加回数が最も多かった商品の最新イベント参加時間を求める

select a.goods_id,a.time
from event a
inner join
(
    select goods_id,count(*)
    from event
    group by gooods_id
    order by count(*) desc
    limit 1
) b
on a.goods_id = b.goods_id
order by a.goods_id,a.time desc

第四の主題

ユーザーログインのログデータは、セッションを指定し、1時間以内の同じユーザーのログインはセッションとみなされます。

sessionカラムの生成

drop table if exists koo.nil_temp0222_a2;
create table if not exists koo.nil_temp0222_a2 as
select *
    ,row_number() over(partition by userid order by inserttime) as nn1
from
(
    select a.*
    ,b.inserttime as inserttime_aftr
    ,datediff(b.inserttime,a.inserttime) as session_diff
  from
  (
    select userid,inserttime
      ,row_number() over(partition by userid order by inserttime asc) nn
    from koo.nil_temp0222
    where userid = 1900000169
  ) a
  left join
  (
     select userid,inserttime
      ,row_number() over(partition by userid order by inserttime asc) nn
    from koo.nil_temp0222
    where userid = 1900000169
  ) b
  on a.userid =  b.userid and a.nn = b.nn-1
) aa
where session_diff >10 or nn = 1
order by userid,inserttime;

drop table if exists koo.nil_temp0222_a2_1;
create table if not exists koo.nil_temp0222_a2_1 as
select a.*
,case when b.nn is null then a.nn+3 else b.nn end as nn_end
from koo.nil_temp0222_a2 a
left join koo.nil_temp0222_a2 b
on a.userid = b.userid
and a.nn1 = b.nn1 - 1;

select a.*,b.nn1 as session_id
from
(
  select userid,inserttime
    ,row_number() over(partition by userid order by inserttime asc) nn
  from koo.nil_temp0222
  where userid = 1900000169
) a
left join koo.nil_temp0222_a2_1 b
on a.userid = b.userid
and a.nn>=b.nn
and a.nn<b.nn_end

第五のテーマ

注文テーブル、フィールドに注文番号と時刻があります。

毎月の最終日に最後の3つの注文を取る。

select *
from
(
  select *
  ,rank() over(partition by mm order by dd desc) as nn1
  ,row_number() over(partition by mm,dd order by inserttime desc) as nn2
  from
  (select cast(right(to_date(inserttime),2) as int) as dd,month(inserttime) as mm,userid,inserttime
  from koo.nil_temp0222) aa
) bb
where nn1 = 1 and nn2<=3;

第六のテーマ

データベース表 Touristsは、7月の1日あたりの観光客数を記録しています。

id date visits 1 2017-07-01 100非常に偶然にも、idフィールドは日付の中の番号と同じです。

連続した3日間で100日を超える日付をフィルタリングします。

上記の例の出力は、date 2017-07-01となります。

select a.*,b.num as num2,c.num as num3
from table  a
left join table b
on a.userid = b.userid
and a.dt = date_add(b.dt,-1)
left join table c
on a.userid = c.userid
and a.dt = date_add(c.dt,-2)
where b.num>100
and a.num>100
and c.num>100

第七のテーマ

既存のAテーブルは、21列、最初の列 ID、残りの列特性フィールド、d 1-d 20からの列名、10Wのデータの合計を持っています。

もう一つのテーブルBはスキーマテーブルと呼ばれ、テーブルAと同じ構造で、合計5Wのデータがあります。

テーブルAの特徴がテーブルBのパターンと一致するデータを見つけ、対応するIDを記録してください。

要件を満たす場合は2つあります：

• 各フィーチャー列が完全に一致した場合
• 最大1つのフィーチャー列が一致せず、他の19のフィーチャー列が完全に一致しますが、どの列が一致しないかは不明です。

select aa.*
from
(
  select *,concat(d1,d2,d3……d20) as mmd
  from table
) aa
left join
(
  select id,concat(d1,d2,d3……d20) as mmd
  from table
) bb
on aa.id = bb.id
and aa.mmd = bb.mmd

select a.*,sum(d1_jp,d2_jp……,d20_jp) as same_judge
from
(
  select a.*
  ,case when a.d1 = b.d1 then 1 else 0 end as d1_jp
  ,case when a.d2 = b.d2 then 1 else 0 end as d2_jp
  ,case when a.d3 = b.d3 then 1 else 0 end as d3_jp
  ,case when a.d4 = b.d4 then 1 else 0 end as d4_jp
  ,case when a.d5 = b.d5 then 1 else 0 end as d5_jp
  ,case when a.d6 = b.d6 then 1 else 0 end as d6_jp
  ,case when a.d7 = b.d7 then 1 else 0 end as d7_jp
  ,case when a.d8 = b.d8 then 1 else 0 end as d8_jp
  ,case when a.d9 = b.d9 then 1 else 0 end as d9_jp
  ,case when a.d10 = b.d10 then 1 else 0 end as d10_jp
  ,case when a.d20 = b.d20 then 1 else 0 end as d20_jp
  ,case when a.d11 = b.d11 then 1 else 0 end as d11_jp
  ,case when a.d12 = b.d12 then 1 else 0 end as d12_jp
  ,case when a.d13 = b.d13 then 1 else 0 end as d13_jp
  ,case when a.d14 = b.d14 then 1 else 0 end as d14_jp
  ,case when a.d15 = b.d15 then 1 else 0 end as d15_jp
  ,case when a.d16 = b.d16 then 1 else 0 end as d16_jp
  ,case when a.d17 = b.d17 then 1 else 0 end as d17_jp
  ,case when a.d18 = b.d18 then 1 else 0 end as d18_jp
  ,case when a.d19 = b.d19 then 1 else 0 end as d19_jp
  from table a
  left join table b
  on a.id = b.id
) aa
where sum(d1_jp,d2_jp……,d20_jp) = 19

第八のテーマ

商品に対するユーザーの評価は、データベーステーブルtに保存されるスパースベクトルで表現されます。

• tのフィールドはuid，goods_id，starです。uidはユーザー IDです。
• goodsidは商品 id = starは1-5の値であるこの商品のユーザー評価です。

ここで、2つのベクトル間の内積を計算したいと思います。内積の意味は次のとおりです。

2人の異なるユーザーが同じ商品のバッチにスコアを付けた場合、その中の各ユーザーのスコアを乗算し、それらの積を合計します。

例えば、データベースには以下のデータが含まれます。

U0 g0 2
U0 g1 4
U1 g0 3
U1 g1 1

計算後の結果は：

U0 U1 23+41=10 ……

select aa.uid1,aa.uid2
,sum(star_multi) as result
from
(
  select a.uid as uid1
  ,b.uid as uid2
  ,a.goods_id
  ,a.star * b.star as star_multi
  from t a
  left join t b
  on a.goods_id = b.goods_id
  and a.udi<>b.uid
) aa
group by 1,2

select uid1,uid2,sum(multiply) as result
from
(select t.uid as uid1, t.uid as uid2, goods_id,a.star*star as multiply
from a left join b
on a.goods_id = goods_id
and a.uid<>uid) aa
group by goods

第九のテーマ

数値と頻度の山を示し、その山の中央値を数えます。

select a.*
,b.s_mid_n
,c.l_mid_n
,avg(b.s_mid_n,c.l_mid_n)
from
(
  select
  case when mod(count(*),2) = 0 then count(*)/2 else (count(*)+1)/2 end as s_mid
  ,case when mod(count(*),2) = 0 then count(*)/2+1 else (count(*)+1)/2 end as l_mid
  from table
) a
left join
(
  select id,num,row_number() over(partition by id order by num asc) nn
  from table
) b
on a.s_mid = b.nn
left join
(
  select id,num,row_number() over(partition by id order by num asc) nn
  from table
) c
on a.l_mid = c.nn

10番目の問題

テーブルorderには、店舗ID、注文時間、注文金額の3つのフィールドがあります。

1 ヶ月以内に毎週販売している店舗を検索する

select distinct credit_level
from
(
  select credit_level,count(distinct nn) as number
  from
  (
    select userid,credit_level,inserttime,month(inserttime) as mm
    ,weekofyear(inserttime) as week
    ,dense_rank() over(partition by credit_level,month(inserttime) order by weekofyear(inserttime) asc) as nn
    from koo.nil_temp0222
    where substring(inserttime,1,7) = '2019-12'
    order by credit_level ,inserttime
  ) aa
  group by 1
) bb
where number = (select count(distinct weekofyear(inserttime))
from koo.nil_temp0222
where substring(inserttime,1,7) = '2019-12')