每天几道SQL题（4）

拼多多笔试题还有一个第三问没有写

1）计算网络每日新增访客表（在这次访问之前没有访问过该网站）；
2）新增访客的第2日、第30日回访比例。

1）
i）每日新增访客数目，count，groupby
ii）如何表示每日新增呢，user_id没有出现在之前的记录
下面两段代码都是参考网友的，我自己写出来总有各种小瑕疵

SELECT t2.log_time,count(distinct t2.user_id)
from tracking_log t2
where t2.user_id 
NOT in (select t1.user_id from tracking_log t1 where t1.log_time<t2.log_time) 
group by t2.log_time

要查出user_id

SELECT distinct(t2.log_time),t2.user_id 
from tracking_log t2
where t2.log_time 
=(select min(t1.log_time) from tracking_log t1 where t1.user_id=t2.user_id) 

第一段代码很好懂，第二段不懂的是子查询里where后面
t1.user_id=t2.user_id，如果是我写是想不到这一条的
按照下面的顺序一层层看
select语句的执行顺序：
from-where-groupby-having-select-distinct-order by
先将t1，t2用user_id连起来，选择最小的日期，也就是限定了每一条满足t1.user_id=t2.user_id中，日期可能是不同的，选出最小日期的那一条，min，max在多个相同值的时候默认返回一条，那这个时候的log_time就是日期最早的那一条

第二题好难啊，而且我的mysql好像版本太低用不了partition，看看小姐姐怎么写的

第一步：
找到第一次访问的时间，按照user分组编号

CREATE VIEW view3 AS
SELECT user_id, log_time,
 MIN(log_time) OVER (PARTITION BY user_id ORDER BY log_time) AS first_log,
    row_number() OVER (PARTITION BY user_id, log_time) AS t
FROM tracking_log

第二步：
去重，每个访客一天可能有多次记录，只记一次
再加一列访问时间和第一次访问时间的差作为留存时间

CREATE VIEW view4 AS
SELECT *, TIMESTAMPDIFF(DAY, first_log, log_time) AS by_day
FROM view3
WHERE t=1;

第三步：数by_day等于0，2，30的人数，计算留存率

CREATE VIEW view5 AS
SELECT first_log,
 sum(case when by_day = 0 then 1 else 0 end) AS day_0,
    sum(case when by_day = 2 then 1 else 0 end) AS day_2,
    sum(case when by_day = 30 then 1 else 0 end) AS day_30
FROM view4
GROUP BY first_log

SELECT first_log, day_2/day_0 AS day2_return, 
	day_30/day_0 AS day30_return FROM view5

总结一下：row_number() over(partition by … order by)用来标号；
MIN(log_time) OVER (PARTITION BY user_id ORDER BY log_time) 后面这一节用于开窗，不一定要和row_number()联用
尝试使用视图，不必嵌套很多层很乱

31-40
31.获取select * from employees对应的执行计划

explain select * from employees

详情

32.将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
CREATE TABLE employees ( emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

要点：字符串连接
CONCAT_WS(separator,str1,str2,…)
例子： select concat_ws(’,’,‘11’,‘22’,‘33’); 　11,22,33
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
例子：select id,group_concat(name separator ‘;’) from aa group by id;
?SQLite 使用的是||,两个||之间的是连接符，用引号括起来

select (last_name ||" "|| first_name) as name from employees e
select concat_ws(space(1),last_name,first_name) as name from employees e

33.建表

create table actor(
actor_id smallint(5) not null ,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update timestamp not null Default (datetime('now','localtime')),
primary key(actor_id))

sqlite要求default 后面加括号再写默认的内容是什么
sqlite里面，datetime（‘now’,‘localtime’)获得当前时间
mysql可以用curdate()

在mysql里面试一下，就在原来那个表里面插入一列

修改表结构：
alter table TableName add column 名称 varchar(50);
更新数据：
update TableName set 名称=‘cor22’;

alter table tracking_log add COLUMN `date` Date
UPDATE `tracking_log` SET `date`=CURDATE()

34.批量插入

insert into actor
values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
      (2,'NICK','WAHLBERG','2006-02-15 12:34:33')

insert into tablename values(),()
不要在tablename之前加table！
alter和create有table，insert和update没有！！

35.插入或忽略
强烈参考
sqlite代码

insert  or ignore  into actor values('3','ED','CHASE','2006-02-15 12:34:33')

mysql

insert ignore into actor values('3','ED','CHASE','2006-02-15 12:34:33')

36.创建临时表
create 表名 as select from old_table
1.这种方式只能复制表内容，不会有任何主键外键约束
2.可以复制表的结构，只要加一条where 1=2

对于新表存在的情况：

原文总结的很好

回归本题

create table actor_name as
select first_name,last_name from actor

37.创建索引
索引这个东西感觉有点抽象，反正就是提高效率的

索引您可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。

参考

create unique index uniq_idx_firstname on actor(first_name);--注意用分号哟
create index idx_lastname on actor(last_name)

创建视图并改名
视观表 (View) 可以被当作是虚拟表格。它跟表格的不同是，表格中有实际储存资料，而视观表是建立在表格之上的一个架构，它本身并不实际储存资料

create view actor_name_view('first_name_v','last_name_v') as
select first_name,last_name from actor

或者

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor

39.又是一道索引
我去查了一下，看到一个通俗的解释
索引是什么，首先我们可以举个例子，字典大家应该都使用过，我们可以使用目录快速定位到所要查找的内容，那么索引跟目录的作用类似，在数据库表记录中，利用索引，可以快速过滤查找到数据记录。
建立了索引之后只是给mysql提供了一个选择，优化系统不一定会使用这个索引，于是可以使用强制索引

sqlite
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005
MySQL中，使用 FORCE INDEX 语句进行强制索引查询，
SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005
————————————————
版权声明：本文为CSDN博主「圣洁不吃冰淇淋」的原创文章，遵循CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_44915703/article/details/97113222

select * from salaries indexed by idx_emp_no where emp_no='10005'

select * from tracking_log force index (idx2) where user_id = '1'

40.插入新的一列，嘿嘿，刚好前几道题有复习到
存在actor表，包含如下列信息：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000-00-00 00:00:00’

alter table actor 
add column 'create_date' datetime not null 
default('0000-00-00 00:00:00')

今天完成了，周一又开始心累，什么时候才能每天都元气满满