使用bito编写SQL

原创

捷后愚生 2024-05-23 09:14:21 博主文章分类：效率工具 ©著作权

©著作权归作者所有：来自51CTO博客作者捷后愚生的原创作品，请联系作者获取转载授权，否则将追究法律责任

第一次提问

作为一个数据库专家，有以下的数据搜索需求： ods.lending_details有idcard_no身份证号、due_bill_no借据号、project_id项目号，ods.loan_info是一个拉链表，ods.loan_info其中有due_bill_no借据号、remain_principal剩余本金、project_id项目号，一个身份证号会有多个借据，ods.lending_details、ods.loan_info可以通过借据号关联。请你使用hivesql实现，查询出每个身份证号下所有借据的剩余本金总额小于等于200000的身份证号，项目号限定为WS10102100950001，快照日限定为2024-03-20，尽可能提高查询性能

以下是您可以使用的Hive SQL查询语句，结合了JOIN和子查询以满足您的需求，同时尽量考虑查询性能：
SELECT ld.idcard_no, SUM(li.remain_principal) AS total_remain_principal
FROM ods.lending_details ld
JOIN ods.loan_info li ON ld.due_bill_no = li.due_bill_no
WHERE ld.project_id = 'WS10102100950001'
  AND li.project_id = 'WS10102100950001'
  AND li.snapshot_date = '2024-03-20'
GROUP BY ld.idcard_no
HAVING SUM(li.remain_principal) <= 200000;
这条SQL语句将根据条件筛选出项目号为WS10102100950001，快照日为2024-03-20的数据，计算每个身份证号下所有借据的剩余本金总额，并筛选出总额小于等于200000的身份证号。

第二次提问

作为一个数据库专家，有以下的数据搜索需求： ods.lending_details有idcard_no身份证号、due_bill_no借据号、project_id项目号，ods.loan_info是一个拉链表，ods.loan_info其中有due_bill_no借据号、remain_principal剩余本金、project_id项目号、s_d_date数据开始日期、e_d_date数据结束日期，一个身份证号会有多个借据，ods.lending_details、ods.loan_info可以通过借据号关联。请你使用hivesql实现，查询出每个身份证号下所有借据的剩余本金总额小于等于200000的身份证号并且去重，只需要查询出身份证号，项目号限定为WS10102100950001，ods.loan_info数据日期过滤条件为'2024-03-20' between s_d_date and date_sub(e_d_date,1)，尽可能提高查询性能

以下是您可以使用的Hive SQL查询语句，结合了JOIN和子查询以满足您的需求，同时尽量考虑查询性能：
SELECT DISTINCT ld.idcard_no
FROM ods.lending_details ld
JOIN (
    SELECT due_bill_no, SUM(remain_principal) AS total_remain_principal
    FROM ods.loan_info
    WHERE project_id = 'WS10102100950001'
      AND '2024-03-20' BETWEEN s_d_date AND date_sub(e_d_date, 1)
    GROUP BY due_bill_no
    HAVING SUM(remain_principal) <= 200000
) li ON ld.due_bill_no = li.due_bill_no
WHERE ld.project_id = 'WS10102100950001';
这条SQL语句将根据条件筛选出项目号为WS10102100950001，数据日期符合要求的数据，计算每个身份证号下所有借据的剩余本金总额，并筛选出总额小于等于200000的身份证号，并去重。

第三次提问

作为一个数据库专家，有以下的数据搜索需求： ods.lending_details有idcard_no身份证号、due_bill_no借据号、project_id项目号，ods.loan_info是一个拉链表，ods.loan_info其中有due_bill_no借据号、remain_principal剩余本金、project_id项目号、s_d_date数据开始日期、e_d_date数据结束日期，一个身份证号会有多个借据，ods.lending_details、ods.loan_info可以通过借据号关联。请你使用hivesql实现，查询出每个身份证号下所有借据的剩余本金总额小于等于200000的身份证号，身份证号要去重，只需要查询出身份证号，项目号限定为WS10102100950001，ods.loan_info数据日期过滤条件为'2024-03-20' between s_d_date and date_sub(e_d_date,1)，尽可能提高查询性能

以下是您可以使用的Hive SQL查询语句，结合了JOIN和子查询以满足您的需求，同时尽量考虑查询性能：
SELECT DISTINCT ld.idcard_no
FROM ods.lending_details ld
JOIN (
    SELECT ld.idcard_no, SUM(li.remain_principal) AS total_remain_principal
    FROM ods.lending_details ld
    JOIN ods.loan_info li ON ld.due_bill_no = li.due_bill_no
    WHERE li.project_id = 'WS10102100950001'
      AND '2024-03-20' BETWEEN li.s_d_date AND date_sub(li.e_d_date, 1)
    GROUP BY ld.idcard_no
    HAVING SUM(li.remain_principal) <= 200000
) sub ON ld.idcard_no = sub.idcard_no
WHERE ld.project_id = 'WS10102100950001';
这条SQL语句将根据条件筛选出项目号为WS10102100950001，数据日期符合要求的数据，计算每个身份证号下所有借据的剩余本金总额，并筛选出总额小于等于200000的身份证号，并去重。

第三次查询符合我的需求。

第四次提问

作为一个数据库专家，有以下的数据搜索需求： ods.lending_details有idcard_no身份证号、due_bill_no借据号、project_id项目号； ods.loan_info是一个拉链表，ods.loan_info其中有due_bill_no借据号、remain_principal剩余本金、project_id项目号、s_d_date数据开始日期、e_d_date数据结束日期，一个身份证号会有多个借据； ods.overdue_statistics_detail有due_bill_no借据号、overdue_terms_count累计逾期期数、dpd_days_count累计逾期天数；请你使用hivesql实现，查询出每个身份证号下所有借据的剩余本金总额小于等于200000、并且要求每个身份证号下的所有借据overdue_terms_count小于等于3和dpd_days_count小于等于30的身份证号，身份证号要去重，只需要查询出身份证号，项目号限定为WS10102100950001，ods.loan_info数据日期过滤条件为'2024-03-20' between s_d_date and date_sub(e_d_date,1)，尽可能提高查询性能

bito回答：

SELECT DISTINCT ld.idcard_no
FROM ods.lending_details ld
JOIN (
    SELECT ld.idcard_no, SUM(li.remain_principal) AS total_remain_principal
    FROM ods.lending_details ld
    JOIN ods.loan_info li ON ld.due_bill_no = li.due_bill_no
    WHERE li.project_id = 'WS10102100950001'
      AND '2024-03-20' BETWEEN li.s_d_date AND date_sub(li.e_d_date, 1)
    GROUP BY ld.idcard_no
    HAVING SUM(li.remain_principal) <= 200000
) sub1 ON ld.idcard_no = sub1.idcard_no
JOIN (  -- 不符合需求，overdue_terms_count <= 3 AND dpd_days_count <= 30可能有身份证号对应借据超过这个条件
    SELECT due_bill_no
    FROM ods.overdue_statistics_detail
    WHERE overdue_terms_count <= 3
      AND dpd_days_count <= 30
) sub2 ON ld.due_bill_no = sub2.due_bill_no
WHERE ld.project_id = 'WS10102100950001';

不符合要求！

文心一言回答

要实现您提出的需求，我们可以先分步分析，再给出最终的HiveSQL查询语句。

首先，我们需要理解几个关键点：

身份证号下的所有借据的剩余本金总额：这需要对身份证号进行分组，并对每个组的剩余本金进行求和。
每个身份证号下的所有借据的逾期期数和逾期天数：同样需要对身份证号进行分组，并对每个组的逾期期数和逾期天数进行条件判断。
数据日期过滤：对ods.loan_info表中的数据开始日期和数据结束日期进行过滤。
项目号限定：只查询项目号为WS10102100950001的记录。

接下来，我们可以编写HiveSQL查询语句：

WITH LoanDetails AS (
    SELECT
        li.idcard_no,
        SUM(li.remain_principal) AS total_remain_principal,
        MAX(osd.overdue_terms_count) AS max_overdue_terms_count,
        MAX(osd.dpd_days_count) AS max_dpd_days_count
    FROM
        ods.lending_details ld
    JOIN
        ods.loan_info li ON ld.due_bill_no = li.due_bill_no
    JOIN
        ods.overdue_statistics_detail osd ON ld.due_bill_no = osd.due_bill_no
    WHERE
        li.project_id = 'WS10102100950001'
        AND '2024-03-20' BETWEEN li.s_d_date AND date_sub(li.e_d_date, 1)
    GROUP BY
        li.idcard_no
)

SELECT DISTINCT
    idcard_no
FROM
    LoanDetails
WHERE
    total_remain_principal <= 200000
    AND max_overdue_terms_count <= 3
    AND max_dpd_days_count <= 30;

这个查询首先使用了一个公共表表达式（CTE）LoanDetails，该CTE对身份证号进行分组，并计算每个身份证号的剩余本金总额、最大逾期期数和最大逾期天数。然后，主查询从这个CTE中选择满足条件的身份证号。

注意：

使用MAX函数来获取每个身份证号的最大逾期期数和最大逾期天数。这是因为如果一个身份证号有多个借据，而我们只需要满足所有借据的逾期条件，那么取最大值就可以确保所有借据都满足条件。
使用DISTINCT来确保查询结果中的身份证号不重复。
在WHERE子句中，我们使用了date_sub函数来确保日期过滤条件正确。

为了提高查询性能，可以考虑以下几点：

确保涉及到的字段（如idcard_no、due_bill_no、project_id、remain_principal、s_d_date、e_d_date、overdue_terms_count和dpd_days_count）都已经建立了索引，特别是用于连接的字段和用于过滤的字段。
如果可能的话，减少表中的数据量，例如只保留近期或相关的数据。
考虑查询的并行执行，如果Hive集群支持的话。

最后，建议在实际环境中对查询进行测试和优化，以确保其满足性能和准确性的要求。

看起来SQL写得不错，似乎符合需求。执行发现：

提问：上面sql执行提示： lnvalid column reference idcard_no，请你分析原因，并优化要实现您提出的需求，我们可以先分步分析，再给出最终的HiveSQL查询语句。

首先，我们需要理解几个关键点：

身份证号下的所有借据的剩余本金总额：这需要对身份证号进行分组，并对每个组的剩余本金进行求和。
每个身份证号下的所有借据的逾期期数和逾期天数：同样需要对身份证号进行分组，并对每个组的逾期期数和逾期天数进行条件判断。
数据日期过滤：对ods.loan_info表中的数据开始日期和数据结束日期进行过滤。
项目号限定：只查询项目号为WS10102100950001的记录。

接下来，我们可以编写HiveSQL查询语句：

WITH LoanDetails AS (
    SELECT
        li.idcard_no,
        SUM(li.remain_principal) AS total_remain_principal,
        MAX(osd.overdue_terms_count) AS max_overdue_terms_count,
        MAX(osd.dpd_days_count) AS max_dpd_days_count
    FROM
        ods.lending_details ld
    JOIN
        ods.loan_info li ON ld.due_bill_no = li.due_bill_no
    JOIN
        ods.overdue_statistics_detail osd ON ld.due_bill_no = osd.due_bill_no
    WHERE
        li.project_id = 'WS10102100950001'
        AND '2024-03-20' BETWEEN li.s_d_date AND date_sub(li.e_d_date, 1)
    GROUP BY
        li.idcard_no
)

SELECT DISTINCT
    idcard_no
FROM
    LoanDetails
WHERE
    total_remain_principal <= 200000
    AND max_overdue_terms_count <= 3
    AND max_dpd_days_count <= 30;

注意：

使用MAX函数来获取每个身份证号的最大逾期期数和最大逾期天数。这是因为如果一个身份证号有多个借据，而我们只需要满足所有借据的逾期条件，那么取最大值就可以确保所有借据都满足条件。
使用DISTINCT来确保查询结果中的身份证号不重复。
在WHERE子句中，我们使用了date_sub函数来确保日期过滤条件正确。

为了提高查询性能，可以考虑以下几点：

确保涉及到的字段（如idcard_no、due_bill_no、project_id、remain_principal、s_d_date、e_d_date、overdue_terms_count和dpd_days_count）都已经建立了索引，特别是用于连接的字段和用于过滤的字段。
如果可能的话，减少表中的数据量，例如只保留近期或相关的数据。
考虑查询的并行执行，如果Hive集群支持的话。

最后，建议在实际环境中对查询进行测试和优化，以确保其满足性能和准确性的要求。

提问：上面sql请你在 ods.lending_details、ods.overdue_statistics_detail加上project_id = 'WS10102100950001'条件为了确保project_id条件被正确地应用到ods.lending_details和ods.overdue_statistics_detail表上，我们需要确保这两个表在连接时都进行了适当的过滤。由于project_id字段可能并不直接存在于ods.overdue_statistics_detail表中，我们假设它存在于ods.lending_details表中，并且我们想要根据这个字段过滤记录。

以下是一个更新后的查询，该查询在连接ods.lending_details和ods.overdue_statistics_detail表时都加入了project_id的条件：

WITH LoanDetails AS (
    SELECT
        ld.idcard_no,
        SUM(li.remain_principal) AS total_remain_principal,
        MAX(osd.overdue_terms_count) AS max_overdue_terms_count,
        MAX(osd.dpd_days_count) AS max_dpd_days_count
    FROM
        (SELECT * FROM ods.lending_details WHERE project_id = 'WS10102100950001') ld
    JOIN
        ods.loan_info li ON ld.due_bill_no = li.due_bill_no
                         AND li.project_id = 'WS10102100950001'
                         AND '2024-03-20' BETWEEN li.s_d_date AND date_sub(li.e_d_date, 1)
    JOIN
        (SELECT * FROM ods.overdue_statistics_detail WHERE due_bill_no IN (SELECT due_bill_no FROM ods.lending_details WHERE project_id = 'WS10102100950001')) osd 
        ON ld.due_bill_no = osd.due_bill_no
    GROUP BY
        ld.idcard_no
)

SELECT DISTINCT
    idcard_no
FROM
    LoanDetails
WHERE
    total_remain_principal <= 200000
    AND max_overdue_terms_count <= 3
    AND max_dpd_days_count <= 30;

在这个查询中，我使用了子查询来首先过滤出project_id = 'WS10102100950001'的记录。对于ods.lending_details表，我在最外层的查询中直接进行了过滤。对于ods.overdue_statistics_detail表，我创建了一个子查询，该子查询从ods.lending_details表中选择了所有具有指定project_id的due_bill_no，然后我用这些due_bill_no来过滤ods.overdue_statistics_detail表中的记录。

注意：如果ods.overdue_statistics_detail表也有project_id字段，并且你想要确保该表中的project_id也符合条件，那么你应该在连接ods.overdue_statistics_detail表的子查询中也加入相应的project_id过滤条件。但是，由于你之前的描述中没有提到这一点，所以我在这个解决方案中没有包含它。如果确实需要，请相应地调整子查询。

最后，请确保在实际环境中运行此查询之前，你已经检查了所有涉及的表结构和字段，以确保它们与查询中的引用相匹配，并且project_id字段确实存在于相应的表中。

提问：每个表都存在project_id，请你优化sql 如果每个表都存在project_id字段，并且你希望确保在连接这些表时都应用project_id = 'WS10102100950001'这个条件，那么你可以直接在JOIN操作中添加这个条件。以下是一个优化后的SQL查询，它确保了project_id条件在连接每个表时都被考虑：

WITH LoanDetails AS (
    SELECT
        ld.idcard_no,
        SUM(li.remain_principal) AS total_remain_principal,
        MAX(osd.overdue_terms_count) AS max_overdue_terms_count,
        MAX(osd.dpd_days_count) AS max_dpd_days_count
    FROM
        ods.lending_details ld
    JOIN
        ods.loan_info li ON ld.due_bill_no = li.due_bill_no
                         AND ld.project_id = li.project_id
                         AND ld.project_id = 'WS10102100950001'
                         AND '2024-03-20' BETWEEN li.s_d_date AND date_sub(li.e_d_date, 1)
    JOIN
        ods.overdue_statistics_detail osd ON ld.due_bill_no = osd.due_bill_no
                                           AND osd.project_id = ld.project_id
    WHERE
        ld.project_id = 'WS10102100950001'
    GROUP BY
        ld.idcard_no
)

SELECT DISTINCT
    idcard_no
FROM
    LoanDetails
WHERE
    total_remain_principal <= 200000
    AND max_overdue_terms_count <= 3
    AND max_dpd_days_count <= 30;

在这个查询中，我做了以下优化：

在连接ods.lending_details和ods.loan_info时，我添加了ld.project_id = li.project_id和ld.project_id = 'WS10102100950001'条件，以确保这两个表的project_id字段匹配，并且都等于指定的值。
在连接ods.lending_details和ods.overdue_statistics_detail时，我添加了osd.project_id = ld.project_id条件，以确保这两个表的project_id字段匹配。由于之前已经在ods.lending_details中过滤了project_id，这个连接条件确保了只有相同project_id的记录会被连接起来。
在WHERE子句中，我保留了ld.project_id = 'WS10102100950001'条件，虽然这在连接条件中已经包含，但保留它可以增加查询的可读性，并且对于某些数据库优化器来说，可能有助于生成更有效的执行计划。

请注意，这个查询假设due_bill_no是连接ods.lending_details、ods.loan_info和ods.overdue_statistics_detail三个表的正确字段。如果实际情况有所不同，你需要根据实际的表结构和字段来调整连接条件。

最后，确保在执行此查询之前，检查数据库表的结构和字段名称是否正确，并且这些字段包含所需的数据。

运行结果：sql没有报错，但是查询没有结果，应该是有结果才正确

自己分析原因： ods.overdue statistics_detail表可能不存在数据，所以使用left join，这样修改后可以查询到数据

第五次提问

要实现这个查询需求，我们需要联接三个表，并且进行分组和过滤操作。下面将分别给出使用WITH语句和创建视图两种方案。

方案一：使用WITH语句

WITH LoanDetails AS (
    SELECT
        ld.idcard_no,
        SUM(li.remain_principal) AS total_remain_principal,
        MAX(osd.overdue_terms_count) AS max_overdue_terms_count,
        MAX(osd.dpd_days_count) AS max_dpd_days_count
    FROM
        ods.lending_details ld
    JOIN
        ods.loan_info li ON ld.due_bill_no = li.due_bill_no AND ld.project_id = li.project_id
    JOIN
        ods.overdue_statistics_detail osd ON ld.due_bill_no = osd.due_bill_no AND ld.project_id = osd.project_id
    WHERE
        ld.project_id = 'WS10102100950001'
        AND li.project_id = 'WS10102100950001'
        AND osd.project_id = 'WS10102100950001'
        AND '2024-03-20' BETWEEN li.s_d_date AND date_sub(li.e_d_date, 1)
    GROUP BY
        ld.idcard_no
)

SELECT DISTINCT
    idcard_no
FROM
    LoanDetails
WHERE
    total_remain_principal > 200000
    OR max_overdue_terms_count > 3
    OR max_dpd_days_count > 30;

方案二：创建视图

首先，创建一个视图LoanDetailsView，该视图包含了每个身份证号的借据剩余本金总额、最大逾期期数和最大逾期天数。

CREATE VIEW LoanDetailsView AS
SELECT
    ld.idcard_no,
    SUM(li.remain_principal) AS total_remain_principal,
    MAX(osd.overdue_terms_count) AS max_overdue_terms_count,
    MAX(osd.dpd_days_count) AS max_dpd_days_count
FROM
    ods.lending_details ld
JOIN
    ods.loan_info li ON ld.due_bill_no = li.due_bill_no AND ld.project_id = li.project_id
JOIN
    ods.overdue_statistics_detail osd ON ld.due_bill_no = osd.due_bill_no AND ld.project_id = osd.project_id
WHERE
    ld.project_id = 'WS10102100950001'
    AND li.project_id = 'WS10102100950001'
    AND osd.project_id = 'WS10102100950001'
    AND '2024-03-20' BETWEEN li.s_d_date AND date_sub(li.e_d_date, 1)
GROUP BY
    ld.idcard_no;

然后，使用这个视图进行查询：

SELECT DISTINCT
    idcard_no
FROM
    LoanDetailsView
WHERE
    total_remain_principal > 200000
    OR max_overdue_terms_count > 3
    OR max_dpd_days_count > 30;

性能优化建议

索引优化：确保ods.lending_details的due_bill_no、project_id、idcard_no字段，ods.loan_info的due_bill_no、remain_principal、project_id、s_d_date、e_d_date字段以及ods.overdue_statistics_detail的due_bill_no、overdue_terms_count、dpd_days_count、project_id字段都已经建立了索引，这可以加速联接和过滤操作。
分区优化：如果ods.loan_info表数据量很大，可以考虑按project_id和due_bill_no进行分区，这样在查询时Hive可以只扫描相关分区的数据，提高查询效率。
数据过滤顺序：在JOIN操作之前，尽可能先过滤掉不符合条件的数据，减少JOIN操作的数据量。
使用DISTINCT减少数据量：在最后的查询中使用DISTINCT关键字可以去除重复的身份证号，但这通常会增加计算开销。如果身份证号是唯一标识符，且没有重复的必要，那么在视图中计算聚合数据时可以只返回不重复的身份证号，以避免在最后一步再进行去重操作。
子查询与JOIN的选择：在某些情况下，使用子查询可能会比使用JOIN更快，但这也取决于数据的大小和分布。在上面的WITH语句方案中，我选择了JOIN操作，因为可以一次性完成数据的联接和聚合，但在具体实现时也可以尝试使用子查询并比较性能。
调整Hive配置：根据集群的硬件资源和数据大小，适当调整Hive的查询执行相关配置，比如调整map和reduce任务的数量、内存分配等，可以进一步提升查询性能。
分析执行计划：使用Hive的EXPLAIN命令分析查询的执行计划，查看是否有不必要的操作或可以优化的地方。