ASPIRE

Hive SQL如何发现日期较新但软件版本较老的情况

=Start=

缘由：

简单整理一下，方便后面有需要的时候参考。核心思路很简单，就是按照日期排序，并检查每个日期的软件版本是否比之前的版本更旧。

正文：

参考解答：
/*
注意事项：
1. 先做数据清洗和去重
2. 比较需要基于相同的设备类型来比较，比如 Mac 的和 Mac 的比较、Windows和Windows的比较
3. 比较需要基于同一个人的同一类设备来比较，所以 lag 要结合 PARTITION BY 进行适当分区
*/
with user_version_info as (
SELECT DISTINCT
  substr(`timestamp`,1,10) as dt,
  user_name,
  regexp_extract(ua, '(Chrome/[^ ]+)', 1) AS software_ver,
  split(replace(ua, ')', '('),'\\(')[1] as ua1
,ua
,case
when ua like '%iPhone%' then 'iPhone'
when ua like '%Android%' then 'Android'
when ua like '%Windows%' then 'Windows'
when ua like '%Macintosh%' then 'Macintosh'
else 'unknown'
end as device_tag
FROM
  ixyzero.access_logs
WHERE
  date BETWEEN '20250101' AND '20250101'
  and ua not like '%keywords%' --用于排除从特定客户端发起的请求（因为它们的Chrome浏览器版本太低，会影响正常分析）
)
,cleaned_logs as (
    SELECT
        user_name,
        device_tag,ua1,ua,
        dt, software_ver
        ,lag(dt) OVER(PARTITION BY user_name,device_tag ORDER BY dt) as last_dt
        ,lag(software_ver) OVER(PARTITION BY user_name,device_tag ORDER BY dt) as last_ver
    FROM
        user_version_info
)

SELECT
*
FROM
cleaned_logs
WHERE software_ver < last_ver
and dt <> last_dt --用于排除升级的那一天(同时存在高低2个版本)

以下是借助通义千问来思考和解决问题的过程：

  1. hive SQL如何提取日志UserAgent字段中的特定字符串？
-- 常规的UserAgent字段内容一般长这样：
-- Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36

,regexp_extract(ua, '(Chrome/[^ ]+)', 1) AS chrome_ver --提取Chrome浏览器版本
,split(replace(ua, ')', '('),'\\(')[1] as ua1 --提取第一个括号中的内容
  1. hive SQL如何分析定位出那种日期比较新，但是使用的软件版本却更老的情况呢？

比如一行日志中的有多个字段 date 表示日期，software_version 表示软件版本，如何分析出那些有新旧版本软件交替使用的情况呢？

在 Hive SQL 中分析和定位日期较新但软件版本较老的情况，可以通过以下步骤实现。核心思路是按照日期排序，并检查每个日期的软件版本是否比之前的版本更旧。

假设表结构

假设你的表名为 logs，包含以下字段：

  • date：表示日期（格式为 YYYY-MM-DD 或其他可比较的格式）。
  • software_version：表示软件版本号（例如 1.2.3 或 v1.2.3）。

为了便于分析，我们需要确保：

  1. 日期字段是可比较的（如字符串格式的日期需要转换为日期类型）。
  2. 版本号字段是可比较的（需要将版本号从字符串格式转换为数值或标准化格式）。

解决方案

1. 数据准备：标准化日期和版本号

如果 date 和 software_version 是字符串格式，我们需要先将其标准化：

  • 将 date 转换为日期类型。
  • 将 software_version 标准化为可比较的形式（如分隔符替换、拆分为多个字段等）。
-- 示例：标准化日期和版本号
SELECT
  date,
  software_version,
  from_unixtime(unix_timestamp(date, 'yyyy-MM-dd')) AS standardized_date, -- 转换为日期类型
  regexp_replace(software_version, '[^0-9.]', '') AS clean_version -- 移除非数字和点的字符
FROM logs;

2. 按日期排序并比较版本号

接下来，我们按日期排序，并比较每个记录的版本号与其前一条记录的版本号，找出日期较新但版本号较老的情况。

WITH ranked_logs AS (
  SELECT
    date,
    software_version,
    LAG(software_version) OVER (ORDER BY date) AS prev_version, -- 获取前一条记录的版本号
    LAG(date) OVER (ORDER BY date) AS prev_date -- 获取前一条记录的日期
  FROM logs
),
cleaned_logs AS (
  SELECT
    date,
    software_version,
    prev_version,
    prev_date,
    CAST(regexp_replace(software_version, '[^0-9.]', '') AS STRING) AS clean_current_version, -- 清洗当前版本号
    CAST(regexp_replace(prev_version, '[^0-9.]', '') AS STRING) AS clean_prev_version -- 清洗前一条记录的版本号
  FROM ranked_logs
)
SELECT
  date,
  software_version,
  prev_date,
  prev_version
FROM cleaned_logs
WHERE clean_current_version < clean_prev_version; -- 当前版本号比前一条记录的版本号更旧

解释

  1. LAG 函数：
  • LAG(column) 是窗口函数，用于获取当前行之前的一行数据（按指定的排序规则）。
  • 在这里，我们使用 LAG 来获取前一条记录的日期和版本号。
  1. 版本号比较：
  • 使用 regexp_replace 清洗版本号，移除非数字和点的字符（例如从 v1.2.3 提取 1.2.3）。
  • 将清洗后的版本号转换为字符串或数值形式进行比较。
  1. 过滤条件：
  • WHERE clean_current_version < clean_prev_version：筛选出当前版本号比前一条记录版本号更旧的情况。

示例数据与结果

输入数据

datesoftware_version
2023-01-01v1.2.3
2023-01-02v1.2.5
2023-01-03v1.2.4

查询结果

datesoftware_versionprev_dateprev_version
2023-01-03v1.2.42023-01-02v1.2.5

解释：

  • 在 2023-01-03 的记录中，版本号 v1.2.4 比前一天的 v1.2.5 更旧，因此被筛选出来。

注意事项

  1. 版本号格式：
  • 如果版本号格式复杂（如包含字母、特殊字符或非标准格式），需要进一步清洗和标准化。
  • 对于多段版本号（如 1.2.3.4），可以考虑将其拆分为多个字段（如 major, minor, patch），然后逐段比较。
  1. 性能优化：
  • 如果数据量较大，建议对 date 字段建立索引，或者根据实际需求分区表以提高查询效率。
  1. 时间范围限制：
  • 如果只需要分析特定时间段的数据，可以在查询中添加 WHERE date BETWEEN ... 进行过滤。

通过上述方法，你可以有效地分析和定位日期较新但软件版本较老的情况，从而发现潜在的问题或异常行为。

question
表示软件版本的software_version是多段版本号格式的，且需要以数值形式进行比较，请给出完整可用的SQL代码

answer

在 Hive SQL 中，如果 software_version 是多段版本号格式（例如 1.2.3.4），并且需要以数值形式进行比较，可以通过以下步骤实现：

  1. 标准化版本号：将多段版本号拆分为多个字段（如 major, minor, patch 等）。
  2. 逐段比较：按照优先级（从左到右）逐段比较每个字段的数值大小。
  3. 使用窗口函数：利用 LAG 函数获取前一条记录的版本号，并进行逐段比较。

以下是完整的 SQL 代码示例：

假设表结构

假设你的表名为 logs，包含以下字段：

  • date：日期字段（格式为 YYYY-MM-DD）。
  • software_version：软件版本号字段（格式为 1.2.3.4 或类似格式）。

完整 SQL 代码

WITH parsed_logs AS (
  -- 解析版本号为多个字段
  SELECT
    date,
    software_version,
    CAST(SPLIT(software_version, '\\.')[0] AS INT) AS major, -- 主版本号
    CAST(SPLIT(software_version, '\\.')[1] AS INT) AS minor, -- 次版本号
    CAST(SPLIT(software_version, '\\.')[2] AS INT) AS patch, -- 修订版本号
    CAST(SPLIT(software_version, '\\.')[3] AS INT) AS build  -- 构建版本号（可选）
  FROM logs
),
ranked_logs AS (
  -- 使用窗口函数获取前一条记录的版本信息
  SELECT
    date,
    software_version,
    major,
    minor,
    patch,
    build,
    LAG(major) OVER (ORDER BY date) AS prev_major, -- 前一条记录的主版本号
    LAG(minor) OVER (ORDER BY date) AS prev_minor, -- 前一条记录的次版本号
    LAG(patch) OVER (ORDER BY date) AS prev_patch, -- 前一条记录的修订版本号
    LAG(build) OVER (ORDER BY date) AS prev_build, -- 前一条记录的构建版本号
    LAG(date) OVER (ORDER BY date) AS prev_date   -- 前一条记录的日期
  FROM parsed_logs
)
-- 筛选出日期较新但版本号较旧的情况
SELECT
  date,
  software_version,
  prev_date,
  CONCAT_WS('.', prev_major, prev_minor, prev_patch, prev_build) AS prev_software_version
FROM ranked_logs
WHERE
  major < prev_major OR
  (major = prev_major AND minor < prev_minor) OR
  (major = prev_major AND minor = prev_minor AND patch < prev_patch) OR
  (major = prev_major AND minor = prev_minor AND patch = prev_patch AND build < prev_build);

解释

1. 解析版本号

  • 使用 SPLIT 函数将 software_version 字符串按点号 . 分隔成多个部分。
  • 将每个部分转换为整数类型（CAST(... AS INT)），以便进行数值比较。
  • 例如，对于版本号 1.2.3.4：
    • major = 1
    • minor = 2
    • patch = 3
    • build = 4

2. 窗口函数获取前一条记录

  • 使用 LAG 函数获取当前记录的前一条记录的版本信息（包括主版本号、次版本号等）。
  • LAG 的排序依据是 date 字段，确保我们按时间顺序分析数据。

3. 逐段比较版本号

  • 按照优先级逐段比较版本号：
    • 首先比较主版本号（major）。
    • 如果主版本号相同，则比较次版本号（minor）。
    • 如果次版本号也相同，则比较修订版本号（patch）。
    • 如果修订版本号仍然相同，则比较构建版本号（build）。
  • 如果当前记录的版本号在任意一段上小于前一条记录的版本号，则认为当前记录的版本号更旧。

4. 筛选条件

  • WHERE 子句用于筛选出日期较新但版本号较旧的情况。

示例数据与结果

输入数据

datesoftware_version
2023-01-011.2.3.4
2023-01-021.2.5.0
2023-01-031.2.4.9

查询结果

datesoftware_versionprev_dateprev_software_version
2023-01-031.2.4.92023-01-021.2.5.0

解释：

  • 在 2023-01-03 的记录中，版本号 1.2.4.9 比前一天的 1.2.5.0 更旧（因为 patch=4 小于 patch=5），因此被筛选出来。

注意事项

  1. 版本号长度不一致：
  • 如果某些版本号缺少某些段（如 1.2 或 1.2.3），可以使用默认值填充缺失的段。例如：
    sql COALESCE(CAST(SPLIT(software_version, '\\.')[3] AS INT), 0) AS build
    这样可以确保所有版本号都有完整的四段。
  1. 性能优化：
  • 如果数据量较大，建议对 date 字段建立索引，或者根据实际需求分区表以提高查询效率。
  1. 复杂版本号格式：
  • 如果版本号中包含非数字字符（如 v1.2.3 或 1.2-beta），需要先清洗版本号，移除非数字和点的字符。

通过上述方法，你可以有效地分析和定位日期较新但软件版本较老的情况，从而发现潜在的问题或异常行为。

参考链接：

通义千问
https://tongyi.aliyun.com/qianwen/

DeepSeek
https://chat.deepseek.com/

=END=

Docker

, ,
, , , , ,

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注