同学用我的面试题拿了某大厂Offer，我却挂了？

王知无-import_bigdata

发布于 2023-09-06 15:15:51

3360

这是一个读者自己整理的面试题问题，同学去面试某大厂，遇到了原题，结果通过了，但是整理面试题的人却挂了？

同比和环比计算

测试数据

-- 创建表并插入数据
CREATE TABLE `saleorder`  (
  `order_id` int ,
  `order_time` date ,
  `order_num` int
) 

-- ----------------------------
-- Records of saleorder
-- ----------------------------
INSERT INTO `saleorder` VALUES 
(1, '2020-04-20', 420),
(2, '2020-04-04', 800),
(3, '2020-03-28', 500),
(4, '2020-03-13', 100),
(5, '2020-02-27', 300),
(6, '2020-01-07', 450),
(7, '2019-04-07', 800),
(8, '2019-03-15', 1200),
(9, '2019-02-17', 200),
(10, '2019-02-07', 600),
(11, '2019-01-13', 300);

与上年度数据对比称"同比"，与上月数据对比称"环比"。相关公式如下:

同比增长率计算公式
(当年值-上年值)/上年值x100% 

环比增长率计算公式
(当月值-上月值)/上月值x100%

环比

select 
    now_month,
    now_num,
    last_num,
    round( (now_num-last_num) / last_num, 2 ) as ratio
FROM 
(
    select 
        now_month,
        now_num, 
        lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num 
    from 
    (
        select 
            substr(order_time, 1, 7) as now_month, 
            sum(order_num) as now_num 
        from saleorder 
        group by 
            substr(order_time, 1, 7) 
    ) t1
) t2;

同比

利用date_add()生成跨年时间

SELECT
 t1.now_month,
    nvl ( now_num, 0 ) AS now_num,
 nvl ( last_num, 0 ) AS last_num,
 nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratio 
FROM
(
 SELECT
  DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
  sum( order_num ) AS now_num 
 FROM
  saleorder 
 GROUP BY
  DATE_FORMAT( order_time, 'yyyy-MM' ) 
) t1
LEFT JOIN 
(
 SELECT
  DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
  sum( order_num ) AS last_num 
 FROM
  saleorder 
 GROUP BY
 DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) 
) AS t2 ON t1.now_month = t2.now_month;

连续三次出现的数字

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。返回的结果表中的数据可以按任意顺序排列。

查询结果格式如下面的例子所示：

Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 是唯一连续出现至少三次的数字。

关联去重法

思路：DISTINCT 和 WHERE 语句

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

row_number()

思路：

找到连续的特点（e.g. 行号 - 分组行号 = k）根据值和特点进行Group By Having筛选连续次数做出指定输出（e.g. 输出连续3次出现的数字，输出用户连续登录3天以上连续的次数、用户数...）

-- 外部排重（如果是要记连续次数的情况，就进行套一层Group By Num）
SELECT DISTINCT Num "ConsecutiveNums"
FROM (
    SELECT  Num,
            /*
             * 连续出现的数特点为：[行号] - [组内行号] = k
             */
            (row_number() OVER (ORDER BY id ASC) - 
                row_number() OVER (PARTITION BY Num ORDER BY id ASC)) AS series_id
    FROM Logs
) tab
-- 根据每个连续情况进行分组，e.g. 开头的1 1 1连续会被记为{数值Num：1, 行号与组内行号差值：0}组
GROUP BY Num, series_id
HAVING COUNT(1) >= 3  -- 连续重复次数

LEAD()函数

思路：

使用窗口函数的偏差函数完美实现。可以这样理解：将num复制两列num1和num2，然后num1整体向上移动一行，num2整体向上移动两行，如下:

所以只要num=num1=num2即可。

select distinct num as ConsecutiveNums  from
(
    select num, lead(num,1) over() as num1, lead(num,2) over() as num2 
    from logs
) as t1
where t1.num = t1.num1 and t1.num1 = t1.num2;

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-08-02，如有侵权请联系 cloudcommunity@tencent.com 删除

time