拼多多2022數(shù)據(jù)分析筆試（0822）

一、選擇題

1.已知樣本量n，樣本均值及方差求置信區(qū)間

2.決策樹

3.峰度系數(shù)

4.協(xié)方差

5.第一、第二熵變

6.充分統(tǒng)計(jì)量

7.xgboost

8.方差分析中的多重比較

二、編程題

1. 一張用戶點(diǎn)擊路徑的表，找出某一日用戶路徑為店鋪頁(yè)-商詳頁(yè)-下單頁(yè)的用戶數(shù)（本人用的是笛卡爾積三張表where限制條件通過）

CREATE TABLE log_info (
????? uid varchar(255)
??? , page_name varchar(255)
??? , starttime varchar(255)
??? , rnk int
??? , dt date
??? )
;

INSERT INTO log_info
??? (uid,page_name,starttime,rnk,dt)
VALUES
??? ('un670', '首頁(yè)', '2021-08-10 08:01:00',1,'2021-08-10'),
??? ('un670', '商詳頁(yè)', '2021-08-10 08:01:10',2,'2021-08-10'),
??? ('un670', '店鋪頁(yè)', '2021-08-10 08:01:20',3,'2021-08-10'),
??? ('un670', '商詳頁(yè)', '2021-08-10 08:01:30',4,'2021-08-10'),
??? ('un670', '下單頁(yè)', '2021-08-10 08:01:40',5,'2021-08-10'),
??? ('un123', '首頁(yè)', '2021-08-10 00:02:00',1,'2021-08-10'),
??? ('un123', '商詳頁(yè)', '2021-08-10 00:03:00',2,'2021-08-10'),
??? ('un123', '店鋪頁(yè)', '2021-08-10 00:04:00',3,'2021-08-10'),
??? ('un123', '商詳頁(yè)', '2021-08-10 00:05:00',4,'2021-08-10'),
??? ('un123', '商詳頁(yè)', '2021-08-10 00:06:00',5,'2021-08-10')
;

2021-08-10|1

2. 一張活躍商品表，找出2021-08-02和2021-08-03新增活躍商品數(shù)，新增活躍商品數(shù)定義為今日活躍但昨日不活躍的商品（本人用（date,goods_id） not in （選出今日活躍昨日也活躍的商品)作為限制通過）

-- CREATE DATABASE test;
-- use test;
CREATE TABLE act_goods_d(
???? stat_date? DATE
??? ,goods_id?? BIGINT
);
?? ?
INSERT INTO act_goods_d
??? (stat_date, goods_id)
VALUES
?('2021-08-01', 27923)
,('2021-08-01', 23456)
,('2021-08-01', 86534)
,('2021-08-02', 27923)
,('2021-08-02', 23456)
,('2021-08-02', 23545)
,('2021-08-03', 23456)
,('2021-08-03', 23545)
,('2021-08-03', 98213)
;

2021-08-02|1
2021-08-03|1

3. 一張用戶點(diǎn)擊表，一張用戶下單表，找出某一日點(diǎn)擊商品數(shù)和引導(dǎo)下單量，引導(dǎo)下單量定義為在同一日點(diǎn)擊商品到下單的單量，注意點(diǎn)擊時(shí)間要早于下單時(shí)間，比較簡(jiǎn)單，要用的date_format

CREATE TABLE flow_clk_i_d (
???? clk_time?? VARCHAR(30)
??? ,uid??????? BIGINT
??? ,goods_id?? BIGINT
);
INSERT INTO flow_clk_i_d
??? (clk_time, uid, goods_id)
VALUES
?('2021-08-06 11:30:50', 2111, 27023)
,('2021-08-07 12:30:56', 2133, 27023)
,('2021-08-07 15:46:23', 2132, 21346)
,('2021-08-07 15:50:56', 2132, 27053)
,('2021-08-07 20:46:23', 2133, 21348)
,('2021-08-07 20:50:56', 2132, 27023)
,('2021-08-08 20:46:23', 2132, 21346)
;
?
CREATE TABLE ordr_goods_i_d (
???? ordr_time VARCHAR(30)
??? ,ordr_id?? BIGINT
??? ,uid?????? BIGINT
??? ,goods_id? BIGINT
);
INSERT INTO ordr_goods_i_d
??? (ordr_time, ordr_id, uid, goods_id)
VALUES
?('2021-08-07 10:48:29', 641841, 2132, 21346)
,('2021-08-07 15:48:29', 642841, 2132, 21346)
,('2021-08-07 12:46:45', 753473, 2132, 27023)
,('2021-08-07 12:46:45', 763473, 2133, 27123)
;

2021-08-07|2|1