SQL 错误解决：Missing columns: 'log_iMonkey_Wechat_overview.CrashIdentifier' - 微信崩溃数据统计

SQL 错误解决：Missing columns: 'log_iMonkey_Wechat_overview.CrashIdentifier' - 微信崩溃数据统计

本文介绍如何解决 SQL 查询错误“Missing columns: 'log_iMonkey_Wechat_overview.CrashIdentifier'”，并提供修复方法。错误原因是查询中缺少必要的列名，可以通过修改子查询中的列名来解决。

错误信息

errormessage: errormessage: 运行SQL失败:[404]运行异常: Code: 47. DB::Exception: Missing columns: 'log_iMonkey_Wechat_overview.CrashIdentifier' while processing query: 'SELECT count(crash_uuid) FROM log_iOS_crash WHERE (crash_uuid = log_iMonkey_Wechat_overview.CrashIdentifier) AND (Ds >= toDateTime('2023-06-12 00:00:00')) AND (Ds <= toDateTime('2023-07-11 23:59:59'))', required columns: 'crash_uuid' 'Ds' 'log_iMonkey_Wechat_overview.CrashIdentifier', maybe you meant: ['crash_uuid','Ds']: While processing (SELECT count(crash_uuid) FROM log_iOS_crash WHERE (crash_uuid = log_iMonkey_Wechat_overview.CrashIdentifier) AND (Ds >= toDateTime('2023-06-12 00:00:00')) AND (Ds <= toDateTime('2023-07-11 23:59:59'))) AS _subquery1124: While processing multiIf((PlatformType AS PlatformType) IN (1, 3, 5, 7), (SELECT count(crash_uuid) FROM log_iOS_crash WHERE (crash_uuid = log_iMonkey_Wechat_overview.CrashIdentifier) AND (Ds >= toDateTime('2023-06-12 00:00:00')) AND (Ds <= toDateTime('2023-07-11 23:59:59'))) AS _subquery1124, PlatformType IN (2, 4, 6, 8), (SELECT count(crashid) FROM log_android_crash WHERE (crashid = log_iMonkey_Wechat_overview.CrashIdentifier) AND (Ds >= toDateTime('2023-06-12 00:00:00')) AND (Ds <= toDateTime('2023-07-11 23:59:59'))) AS _subquery1125, 0) AS index_crashcount_0. (UNKNOWN_IDENTIFIER) (version 52.3.10.8)

错误原因

该错误信息表明，在执行查询时，缺少列'log_iMonkey_Wechat_overview.CrashIdentifier'。这意味着你的查询试图使用一个不存在的列名。

解决方法

要修复此错误，你需要修改查询中的以下部分：

1. 将第一个子查询中的'crash_uuid'更改为'CrashIdentifier'，并确保它与'log_iMonkey_Wechat_overview.CrashIdentifier'进行匹配。
2. 将第二个子查询中的'crashid'更改为'CrashIdentifier'，并确保它与'log_iMonkey_Wechat_overview.CrashIdentifier'进行匹配。

修改后的查询

SELECT 
  PlatformType AS PlatformType,
  (CASE 
    WHEN PlatformType IN (1, 3, 5, 7) THEN (
      SELECT COUNT(CrashIdentifier)
      FROM log_iOS_crash
      WHERE CrashIdentifier = log_iMonkey_Wechat_overview.CrashIdentifier
      AND Ds >= toDateTime('2023-06-12 00:00:00')
      AND Ds <= toDateTime('2023-07-11 23:59:59')
    )
    WHEN PlatformType IN (2, 4, 6, 8) THEN (
      SELECT COUNT(CrashIdentifier)
      FROM log_android_crash
      WHERE CrashIdentifier = log_iMonkey_Wechat_overview.CrashIdentifier
      AND Ds >= toDateTime('2023-06-12 00:00:00')
      AND Ds <= toDateTime('2023-07-11 23:59:59')
    )
    ELSE 0
  END) AS index_crashcount_0
FROM log_iMonkey_Wechat_overview
WHERE Debug = 0 AND PlatformType != 0 AND CrashIdentifier != '' AND
  Ds >= toDateTime('2023-06-12 00:00:00')
  AND Ds <= toDateTime('2023-07-11 23:59:59') 
GROUP BY PlatformType
ORDER BY index_crashcount_0 DESC
LIMIT 5000

注意事项

请注意，你还需要确保'log_iOS_crash'和'log_android_crash'表中存在与'log_iMonkey_Wechat_overview.CrashIdentifier'列匹配的列名。如果列名不匹配，请相应地更改查询中的列名。

通过以上修改，你的查询应该能够正常运行并返回你期望的结果。