SQL Group by聚合查询月度交易时遗漏同账户96条记录问题

你的查询丢数是典型的外连接写法规避不当导致的，具体问题如下：

• 核心触发点：你将connected_accounts_metadata表的过滤条件connected_accounts_metadata.key = 'podium_organization_uid'写在了WHERE子句中。SQL的执行逻辑是先完成FULL JOIN关联，再执行WHERE条件过滤：如果某条交易关联不到对应key='podium_organization_uid'的元数据，关联结果行中connected_accounts_metadata.key的值为NULL，会直接被该条件排除。你丢失的96条交易全部属于同一个账户，恰好是因为这个账户在connected_accounts_metadata表中没有配置podium_organization_uid对应的元数据记录，所以该账户下所有交易都被过滤。
• 附加逻辑问题：你使用FULL JOIN关联维度表完全没有必要，你的核心交易数据全部来自activity_report_itemized主表，FULL JOIN反而可能引入主表不存在的脏数据；另外时间过滤用<= '2022-06-01 00:00:00'会把6月1日0点整的非5月交易包含进来，属于边界逻辑错误，但不会导致本次固定账户的批量丢数。
• 潜在风险：你对payment_intents_metadata这类键值对结构的元数据表直接做FULL JOIN后用MAX聚合，没有提前做行转列过滤，很容易出现不同元数据项交叉匹配导致的字段值错乱问题。

1. 把外连接表的过滤条件移动到JOIN子句中，同时将不必要的FULL JOIN替换为LEFT JOIN（主表保留所有交易，关联不上的维度字段返回NULL即可，不会丢数），关联部分修改为：

from activity_report_itemized 
left join payment_intents_metadata 
  on activity_report_itemized.payment_intent_id = payment_intents_metadata.payment_intent_id
left join connected_accounts_metadata 
  on activity_report_itemized.connected_account_id = connected_accounts_metadata.account
  and connected_accounts_metadata.key = 'podium_organization_uid'
left join payment_method_details 
  on activity_report_itemized.charge_id = payment_method_details.charge_id

2. 修正时间范围的边界逻辑，避免多算6月1日0点的数据：

where activity_report_itemized.balance_transaction_created_at >= to_date('2022-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and activity_report_itemized.balance_transaction_created_at < to_date('2022-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and activity_report_itemized.balance_transaction_reporting_category = 'charge'
and activity_report_itemized.balance_transaction_component = 'gross'

内容的提问来源于stack exchange，提问作者Kaiah Nelson