SELECT a.company_code, a.company_name, a.gl_account_nbr, a.clearing_date, a.clear_account_document_nbr, a.doc_assignment, a.fiscal_year, a.gl_document_nbr, a.document_item_nbr, a.doc_posting_date, a.document_date, a.document_currency, a.doc_reference, a.document_type, a.fiscal_period, a.doc_posting_key, a.doc_account_debit_or_credit, a.business_scope, a.doc_tax_code, a.doc_local_currency_amount, a.doc_currency_amount, a.tax_amount_standard, a.doc_currency_tax, a.doc_item_text, a.doc_source_order_nbr, a.plant_code, a.cost_center, a.doc_payment_starting_date, a.doc_open_item_management, a.doc_value_date, a.bpm_order_no, a.process_code, a.paymethod, a.paystract, a.zshare_ctr, a.cl_document_nbr, a.handling_accountant, a.handling_accountant_name, a.z_proid, a.pernr, a.dr_amount, a.cr_amount, a.paymethod_name, a.gl_account_name, a.anti_accounting_mark, a.enter_time, a.wb_debit_amount, a.wb_crebit_amount, a.ts_ms, a.cnt FROM ( SELECT a_1.company_code, a_1.company_name, a_1.gl_account_nbr, a_1.clearing_date, a_1.clear_account_document_nbr, a_1.doc_assignment, a_1.fiscal_year, a_1.gl_document_nbr, a_1.document_item_nbr, a_1.doc_posting_date, a_1.document_date, a_1.document_currency, a_1.doc_reference, a_1.document_type, a_1.fiscal_period, a_1.doc_posting_key, a_1.doc_account_debit_or_credit, a_1.business_scope, a_1.doc_tax_code, a_1.doc_local_currency_amount, a_1.doc_currency_amount, a_1.tax_amount_standard, a_1.doc_currency_tax, a_1.doc_item_text, a_1.doc_source_order_nbr, a_1.plant_code, a_1.cost_center, a_1.doc_payment_starting_date, a_1.doc_open_item_management, a_1.doc_value_date, a_1.bpm_order_no, head.procode AS process_code, payment.paymethod, a_1.paystract, a_1.zshare_ctr, a_1.cl_document_nbr, CASE WHEN head.pernrjb IS NOT NULL AND head.pernrjb <> ‘’::text THEN head.pernrjb ELSE bkpf.usnam END AS handling_accountant, pa001.sname AS handling_accountant_name, head.proid AS z_proid, head.pernr, a_1.dr_amount, a_1.cr_amount, payment.paymethodtxt AS paymethod_name, a_1.gl_account_name, a_1.anti_accounting_mark, a_1.enter_time, a_1.wb_debit_amount, a_1.wb_crebit_amount, a_1.ts_ms, row_number() OVER (PARTITION BY a_1.company_code, a_1.gl_account_nbr, a_1.fiscal_year, a_1.fiscal_period, a_1.gl_document_nbr, a_1.document_item_nbr ORDER BY head.procode DESC) AS cnt FROM table_01 a_1 LEFT JOIN dwd_ztbpm_payment_view payment ON a_1.bpm_order_no = payment.orderno LEFT JOIN dwd_ztfi_trpay_view trpay ON a_1.bpm_order_no = trpay.orderno LEFT JOIN dwd_bkpf_r bkpf ON bkpf.bukrs = a_1.company_code AND bkpf.gjahr = a_1.fiscal_year AND bkpf.belnr = a_1.gl_document_nbr LEFT JOIN dwd_ztbpm_post_head_r head ON a_1.bpm_order_no = head.orderno LEFT JOIN dwd_pa0001_r pa001 ON pa001.pernr = head.pernrjb) a WHERE a.cnt = 1 用rownumber会导致sql运行较慢，该怎么改能让sql运行快一点且数据结果与之前保持一致 DISTINCT ON 不支持 改完后的sql具体给我写一下

<think>嗯，用户给了一个比较复杂的SQL查询，然后提到使用ROW_NUMBER()导致性能问题，想用DISTINCT ON但数据库不支持，需要优化。首先，我需要理解原查询的结构。 原查询有一个子查询，里面用了ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS cnt，然后在外部筛选cnt=1。这样做的目的是在每个分组内按procode降序排列，取第一条记录，相当于去重。但用户说用ROW_NUMBER()导致速度慢，可能是因为窗口函数在大数据量时效率不高。 用户提到不能用DISTINCT ON，可能是数据库不支持，比如在SQL Server里没有这个语法，而DISTINCT ON是PostgreSQL的特性。所以需要找其他方法替代。 接下来，我需要想优化方法。常见的方法包括使用子查询或者JOIN来替代ROW_NUMBER()。例如，对于每个分组，找到最大的procode，然后通过JOIN回原表，这样可能更高效。 原查询中的PARTITION BY字段是company_code, gl_account_nbr, fiscal_year, fiscal_period, gl_document_nbr, document_item_nbr，按procode DESC排序。所以对于每个分组，需要最大的procode对应的记录。 可以考虑用子查询先找到每个分组的max(procode)，然后和原表关联，这样只保留最大procode的记录。这样可能避免使用窗口函数，提升性能。 不过需要确认原表中的数据是否每个分组中procode唯一，如果有多个相同procode的情况，可能还需要其他字段来排序，或者使用其他方式确保唯一性。 另外，原查询中有多个LEFT JOIN，可能也需要检查这些连接是否有性能问题，比如索引是否存在，连接条件是否有效。但用户的问题主要集中在ROW_NUMBER()部分，所以先处理这部分。 然后，修改后的SQL需要保持结果和原来一致，即每个分组只取一条procode最大的记录，同时保留所有其他字段。使用子查询找到每个分组的max(procode)，然后和原表进行JOIN，这样可能更高效。 可能的改写步骤： 1. 在子查询中，对每个分组找到最大的procode。 2. 将原表与这个子查询JOIN，条件是分组字段和procode相等，这样每个分组只保留最大procode对应的记录。 3. 替换原来的ROW_NUMBER()逻辑，去掉窗口函数。 但需要注意，如果有多个记录在同一个分组且有相同的最大procode，这样会保留多条，而原查询因为ORDER BY procode DESC，并且用了ROW_NUMBER()=1，只会取第一条。如果数据中存在这种情况，可能需要额外的处理，比如增加排序条件，或者确保procode唯一。 不过用户提到需要结果一致，假设原数据中每个分组的procode是唯一的，或者即使有多个，用户接受取其中一条，那么使用子查询方法可以替代。 此外，改写后的SQL需要保持LEFT JOIN的逻辑，可能需要将子查询作为另一个LEFT JOIN，避免过滤掉原本可能为NULL的记录。 或者，在子查询中使用GROUP BY分组字段，并获取max(procode)，然后与原表JOIN，这样每个分组只保留最大procode的记录。 例如，在子查询中： SELECT a_1.company_code, a_1.gl_account_nbr, ..., head.procode, ... FROM table_01 a_1 LEFT JOIN ... 然后，对于每个分组，找到max_procode： SELECT company_code, gl_account_nbr, fiscal_year, fiscal_period, gl_document_nbr, document_item_nbr, MAX(procode) AS max_procode FROM ... GROUP BY company_code, gl_account_nbr, fiscal_year, fiscal_period, gl_document_nbr, document_item_nbr 然后，将原查询与这个子查询JOIN，条件是分组字段相同，且procode = max_procode。 这样，每条记录只会匹配到其分组中procode最大的那条，达到和ROW_NUMBER()=1相同的效果。 不过需要注意，如果原表中有多个记录在同一个分组且有相同的max_procode，这样会返回多条，而ROW_NUMBER()会取一条（取决于排序的其他条件）。因此，可能需要确保在分组中procode是唯一的，或者有其他字段可以进一步筛选。 如果原数据中procode在分组内是唯一的，那么这种方法可以完全替代ROW_NUMBER()。否则，可能需要其他方式处理。 但根据用户的需求，他们希望结果与之前一致，所以假设原来的ROW_NUMBER()在相同procode时取一条，而max(procode)的方式可能会返回多条，这可能导致结果不同。这时候可能需要进一步的处理，比如在子查询中除了max(procode)外，还获取对应的唯一标识，如主键，然后JOIN回去，确保只取一条。 不过这可能比较复杂，而且需要原表有唯一标识。如果用户的数据允许，可以这样做。否则，可能需要其他方法。 回到用户的问题，他们希望改写SQL，不使用ROW_NUMBER()，同时保持结果一致，且DISTINCT ON不可用。所以可能的解决方案是使用上述的GROUP BY和JOIN方法，但需要处理可能的多条记录问题。 另一个可能的优化是使用内联子查询，例如在WHERE子句中关联子查询，找到每个分组的最大procode，并且当前记录的procode等于该最大值。这样可能更高效。 例如： ... FROM table_01 a_1 LEFT JOIN ... WHERE head.procode = ( SELECT MAX(procode) FROM ... WHERE 分组条件相同 ) 不过这种方法可能在性能上不如预期，特别是当子查询需要频繁执行时。 综合考虑，比较可行的方法是在主查询中使用GROUP BY获取每个分组的max(procode)，然后与原表JOIN。不过需要调整SQL结构。 现在，需要将原查询中的ROW_NUMBER()部分替换为这种JOIN方式。同时，确保其他LEFT JOIN的条件正确，不影响结果。 在改写后的SQL中，可能需要将原来的子查询拆分为两个部分：一部分用于获取每个分组的max(procode)，另一部分用于关联其他表，并过滤出对应的记录。 可能需要使用公共表达式（CTE）或者子查询来先获取每个分组的最大procode，然后再关联回主表和其他表。 例如： WITH max_procode AS ( SELECT company_code, gl_account_nbr, fiscal_year, fiscal_period, gl_document_nbr, document_item_nbr, MAX(head.procode) AS max_procode FROM table_01 a_1 LEFT JOIN dwd_ztbpm_post_head_r head ON a_1.bpm_order_no = head.orderno GROUP BY company_code, gl_account_nbr, fiscal_year, fiscal_period, gl_document_nbr, document_item_nbr ) SELECT ... FROM table_01 a_1 LEFT JOIN max_procode mp ON a_1.company_code = mp.company_code AND ... LEFT JOIN dwd_ztbpm_post_head_r head ON a_1.bpm_order_no = head.orderno AND head.procode = mp.max_procode ... 这样，每个分组只关联到最大的procode对应的head记录。不过需要注意，其他LEFT JOIN的条件可能需要调整，确保正确关联。 不过这可能比较复杂，因为原查询中的head表被多次引用，并且可能影响其他字段的计算，如handling_accountant等。 另一种方法是，在原有子查询中，使用相关子查询来获取每个分组的max(procode)，并在外部过滤，只保留那些procode等于max(procode)的记录。 例如，在子查询中添加一个条件： WHERE head.procode = ( SELECT MAX(h2.procode) FROM dwd_ztbpm_post_head_r h2 WHERE h2.orderno = a_1.bpm_order_no -- 可能需要更多的关联条件 ) 不过这可能不准确，因为需要按原查询中的PARTITION BY条件来分组，而这里的子查询可能需要更复杂的关联。 或者，直接在子查询中使用JOIN，将每个分组的max(procode)找出来，然后与原表的head.procode进行比较。 这可能更有效，但需要正确关联所有分组字段。 总的来说，最直接的优化方式是将ROW_NUMBER()窗口函数替换为通过GROUP BY和JOIN来获取每个分组的最大procode记录。这样可以避免窗口函数的计算，提升性能。 现在，根据原查询的PARTITION BY字段是a_1的几个字段，而ORDER BY是head.procode DESC。因此，正确的分组应该是基于a_1中的字段，而procode来自head表。因此，正确的GROUP BY应该基于这些a_1的字段，然后获取对应的head.procode的最大值。 不过这可能涉及到将a_1和head表先进行连接，然后分组。例如： SELECT a_1.company_code, a_1.gl_account_nbr, a_1.fiscal_year, a_1.fiscal_period, a_1.gl_document_nbr, a_1.document_item_nbr, MAX(head.procode) AS max_procode FROM table_01 a_1 LEFT JOIN dwd_ztbpm_post_head_r head ON a_1.bpm_order_no = head.orderno GROUP BY a_1.company_code, a_1.gl_account_nbr, a_1.fiscal_year, a_1.fiscal_period, a_1.gl_document_nbr, a_1.document_item_nbr 然后，将原查询中的子查询与这个max_procode进行JOIN，确保只选择procode等于max_procode的记录。这可能需要将head表再次JOIN，但这次只匹配max_procode。 不过这样的话，原查询中的其他字段（如handling_accountant等）可能来自不同的head记录，但这里需要确保只取max_procode对应的那条head记录。 这可能比较复杂，因为原查询中的其他LEFT JOIN（如payment, trpay, bkpf, pa001等）也需要处理。 但假设其他JOIN不影响分组和max(procode)的选择，那么改写后的SQL可能如下： 在子查询中，先获取每个分组的max_procode，然后JOIN回原表和其他表，并过滤出head.procode = max_procode的记录。 例如： SELECT a_1.company_code, ...其他字段, head.procode AS process_code, ... FROM table_01 a_1 LEFT JOIN dwd_ztbpm_payment_view payment ON ... LEFT JOIN dwd_ztfi_trpay_view trpay ON ... LEFT JOIN dwd_bkpf_r bkpf ON ... LEFT JOIN ( SELECT a_1_sub.company_code, a_1_sub.gl_account_nbr, a_1_sub.fiscal_year, a_1_sub.fiscal_period, a_1_sub.gl_document_nbr, a_1_sub.document_item_nbr, MAX(head_sub.procode) AS max_procode FROM table_01 a_1_sub LEFT JOIN dwd_ztbpm_post_head_r head_sub ON a_1_sub.bpm_order_no = head_sub.orderno GROUP BY a_1_sub.company_code, a_1_sub.gl_account_nbr, a_1_sub.fiscal_year, a_1_sub.fiscal_period, a_1_sub.gl_document_nbr, a_1_sub.document_item_nbr ) AS max_procode ON a_1.company_code = max_procode.company_code AND ...其他分组字段... LEFT JOIN dwd_ztbpm_post_head_r head ON a_1.bpm_order_no = head.orderno AND head.procode = max_procode.max_procode LEFT JOIN dwd_pa0001_r pa001 ON pa001.pernr = head.pernrjb ... 这样，每个分组只取最大的procode对应的head记录，其他JOIN保持不变。这样可能避免使用ROW_NUMBER()，提高性能。 不过需要注意，这样改写后，如果同一个分组中有多条记录有相同的max_procode，那么可能会返回多条记录，而原查询中的ROW_NUMBER()=1只会取一条。因此，可能存在数据不一致的问题。需要确保在分组中，max_procode对应的记录唯一，或者在JOIN时进一步去重。 如果原数据中每个分组的max_procode对应唯一的记录，那么这种方法可行。否则，可能需要其他处理方式，比如在子查询中不仅获取max_procode，还获取对应的唯一标识，如head的主键，然后JOIN回去。 但这样会增加复杂度，可能需要更复杂的子查询。 总结来说，优化后的SQL应该使用GROUP BY和JOIN来替代ROW_NUMBER()，确保每个分组只取一条记录，同时保持其他字段的正确性。这可能需要调整JOIN条件和子查询结构，确保性能提升且结果一致。</think>针对该SQL性能问题的优化方案及修改后的SQL如下： #### 优化思路 1. **ROW_NUMBER性能瓶颈分析** 原SQL使用`ROW_NUMBER() OVER (PARTITION BY ...)`对全表数据进行窗口排序，当数据量较大时排序操作会显著降低性能。 2. **替代方案选择** 虽然`DISTINCT ON`语法不支持，但可以通过`LATERAL JOIN`或`INNER JOIN`结合`MAX()`函数实现相同逻辑，避免全表排序。 3. **关键改写步骤** - 将窗口函数改为先获取每个分组的`MAX(procode)` - 通过`JOIN`操作关联出最大值对应的记录 #### 优化后的SQL ```sql SELECT a.company_code, a.company_name, a.gl_account_nbr, ...（此处完整列出所有字段，与原SQL字段顺序一致） FROM ( SELECT a_1.company_code, a_1.company_name, a_1.gl_account_nbr, ...（此处完整列出子查询所有字段）， head.procode AS process_code, payment.paymethod, ...（其他字段与原SQL一致）， -- 移除ROW_NUMBER() -- row_number() OVER (...) AS cnt FROM table_01 a_1 LEFT JOIN dwd_ztbpm_payment_view payment ON a_1.bpm_order_no = payment.orderno LEFT JOIN dwd_ztfi_trpay_view trpay ON a_1.bpm_order_no = trpay.orderno LEFT JOIN dwd_bkpf_r bkpf ON bkpf.bukrs = a_1.company_code AND bkpf.gjahr = a_1.fiscal_year AND bkpf.belnr = a_1.gl_document_nbr -- 关键改写：通过JOIN获取最大procode对应的记录 LEFT JOIN LATERAL ( SELECT h.procode, h.pernrjb, h.proid, h.pernr FROM dwd_ztbpm_post_head_r h WHERE h.orderno = a_1.bpm_order_no ORDER BY h.procode DESC LIMIT 1 ) head ON true LEFT JOIN dwd_pa0001_r pa001 ON pa001.pernr = head.pernrjb ) a -- 移除原WHERE a.cnt = 1条件 ``` #### 优化原理说明 1. **LATERAL JOIN替代窗口函数** - 使用`LATERAL (SELECT ... LIMIT 1)`为每条主表记录获取最大`procode`对应的明细记录 - 执行计划从全表排序改为逐行关联，避免全量排序操作 2. **性能提升点** - 消除窗口排序操作（`WindowAgg`） - 减少中间结果集的数据量 - 可利用索引加速`dwd_ztbpm_post_head_r.procode`排序 3. **结果一致性保证** - `ORDER BY h.procode DESC LIMIT 1`与原`ROW_NUMBER()...ORDER BY procode DESC`逻辑完全一致 - 关联条件`h.orderno = a_1.bpm_order_no`保持原有业务逻辑 #### 其他优化建议 1. **索引优化** ```sql CREATE INDEX idx_post_head_orderno_procode ON dwd_ztbpm_post_head_r(orderno, procode DESC); ``` 2. **统计信息更新** ```sql ANALYZE table_01; ANALYZE dwd_ztbpm_post_head_r; ``` 3. **执行计划验证** 建议通过`EXPLAIN ANALYZE`对比优化前后的执行计划，重点关注： - 排序操作是否消除 - 嵌套循环是否被更高效的连接方式替代 - 预估行数是否准确 此改写方案在保证结果一致性的前提下，通过改变数据获取方式显著提升性能。实际优化效果需根据具体数据分布验证。