大数据面试题———SQL

1、题目17：车辆每天上传N条数据，要求获得每日最后一条数据，请用至少三种方式写出来，并说明三种方式的区别

vin                pkgts             value
1111111111111111,2022-06-01 16:54:57,60.0
1111111111111111,2022-06-01 07:40:27,6.0
2222222222222222,2022-06-01 19:40:27,7.0
1111111111111111,2022-06-26 12:16:45,64.0
2222222222222222,2022-06-05 23:00:42,18.0
2222222222222222,2022-06-05 09:10:17,44.0
2222222222222222,2022-06-05 13:00:25,19.0
2222222222222222,2022-06-13 16:14:04,43.0
2222222222222222,2022-06-13 07:21:08,40.0

建表语句：

create table t17(
    vin string,
    pkgts string,
    value double
)row format delimited
fields terminated by ',';
-- 加载数据
load data local inpath '/home/hivedata/zuoye/t17.txt' into table t17;

-- 方法一：使用窗口函数

select
    vin,
    pkgts as last_pkgts,
    value as last_value
from (
    select vin, pkgts, value,
        row_number() over (partition by vin, date (pkgts) order by pkgts desc ) as rn
    from t17
) t where rn = 1;

在Hive中，可以使用窗口函数row_number()来为每个车辆每天的数据分配一个序号，其中序号为1的即为每天的最后一条数据。这里，partition by vin, date(pkgts)将数据按照车辆和日期进行分组，order by pkgts desc确保了在每个分组内按时间降序排列，row_number()为每个分组内的记录分配一个唯一的序号。

-- 方法二：使用自连接

这种方法通过将表与其自身进行连接，来比较同一车辆同一天的记录。

select
    t1.vin,
    t1.pkgts as last_pkgts,
    t1.value as last_value
from t17 t1
left join t17 t2
    on t1.vin = t2.vin and date(t1.pkgts) = date(t2.pkgts) and t1.pkgts < t2.pkgts
where t2.vin is null;

这里，我们通过left join将t17表与自身连接，连接条件是车辆 vin 相同且日期相同，但pkgts 时间要早于连接表中的pkgts。最后通过where t2.vin is null条件过滤出没有后续记录的行，即每天的最后一条记录。

-- 方法三：使用聚合函数和子查询 

这种方法首先使用聚合函数max()找到每个车辆每天的最大pkgts时间戳，然后通过子查询将这些时间戳与原表连接，以获取对应的记录。

select
    t1.vin,
    t1.pkgts as last_pkgts,
    t1.value as last_value
from t17 t1
inner join (
    select vin, max(pkgts) as max_pkgts
    from t17
    group by vin,date(pkgts)
) gt on t1.vin = gt.vin and t1.pkgts = gt.max_pkgts;

在这个查询中，子查询首先按车辆和日期分组，然后使用max()函数找到每组的最大pkgts。然后，通过inner join将这些最大时间戳与原表连接，以获取每天最后一条记录。

 三种方法的区别总结：

1. 窗口函数：这种方法是最直观和最常用的，特别是在需要处理大量数据时，窗口函数可以有效地利用数据库的优化和并行处理能力。

2. 自连接：这种方法在逻辑上更简单，但它可能在性能上不如窗口函数，特别是在处理大量数据时，因为自连接需要对数据进行多次扫描。

3. 聚合函数和子查询：这种方法在某些情况下可能更易于理解和实现，特别是对于那些不熟悉窗口函数的用户。然而，它可能需要更多的数据库资源，因为它涉及到多次查询和连接操作。

每种方法都有其适用场景，选择哪种方法取决于具体的数据量、数据库性能和个人偏好。