ClickHouse SQL 错误 'NO_COMMON_TYPE' 解决方法：将整数类型转换为 Int64

ClickHouse SQL 错误 'NO_COMMON_TYPE' 解决方法：将整数类型转换为 Int64

在使用 ClickHouse 进行 SQL 查询时，可能会遇到以下错误：

报错errormessage: 运行SQL失败:[500]运行异常: Code: 386. DB::Exception: There is no supertype for types Int32, UInt64 because some of them are signed integers and some are unsigned integers, but there is no signed integer type, that can exactly represent all required unsigned integer values: While processing toStartOfDay(toDateTime(Ds)) AS datetime_day_Ds, PlatformType AS PlatformType, greatest(countDistinctIf(UrlContent2, PlatformType IN (4)), maxIf(MergedUrlCount, PlatformType IN (3, 4)), maxIf(MergedViewCount, PlatformType NOT IN (3, 4))) AS index_mergedviewcount. (NO_COMMON_TYPE) (version 52.3.10.8)

该错误通常发生在对不同类型的整数进行比较时，例如 Int32 和 UInt64。ClickHouse 不允许直接比较不同类型的整数，因为它们可能具有不同的位数和符号。

解决方法：

将所有整数类型统一为同一种类型，例如将所有整数类型转换为 Int64 类型。

示例：

假设我们遇到了以下 SQL 查询错误：

SELECT toStartOfDay(toDateTime(Ds)) AS datetime_day_Ds,
    PlatformType AS PlatformType,
    GREATEST(
    count(DISTINCT UrlContent2) FILTER (WHERE PlatformType in (4)),
    max(MergedUrlCount) FILTER (WHERE PlatformType in (3,4)),
    max(MergedViewCount) FILTER (WHERE PlatformType not in (3,4))
    ) AS index_mergedviewcount
  FROM log_iMonkey_Wechat_overview
  WHERE 
  Debug = 1 AND 
  PlatformType not IN (0) AND
  Ds >= toDateTime(#day_wechat_views_time.start#)
  AND Ds <= toDateTime(#day_wechat_views_time.end#) + INTERVAL 1 DAY
  GROUP BY datetime_day_Ds, PlatformType
  ORDER BY datetime_day_Ds DESC, PlatformType DESC

可以将所有整数类型转换为 Int64 类型，修改后的 SQL 查询如下：

SELECT toStartOfDay(toDateTime(Ds)) AS datetime_day_Ds,
    PlatformType AS PlatformType,
    GREATEST(
    count(DISTINCT UrlContent2) FILTER (WHERE PlatformType in (4)),
    max(CAST(MergedUrlCount AS Int64)) FILTER (WHERE PlatformType in (3,4)),
    max(CAST(MergedViewCount AS Int64)) FILTER (WHERE PlatformType not in (3,4))
    ) AS index_mergedviewcount
  FROM log_iMonkey_Wechat_overview
  WHERE 
  Debug = 1 AND 
  PlatformType not IN (0) AND
  Ds >= toDateTime(#day_wechat_views_time.start#)
  AND Ds <= toDateTime(#day_wechat_views_time.end#) + INTERVAL 1 DAY
  GROUP BY datetime_day_Ds, PlatformType
  ORDER BY datetime_day_Ds DESC, PlatformType DESC

注意：

• 确保所有整数类型都已正确转换为 Int64 类型，以避免数据丢失或精度问题。
• 您可以根据需要选择其他整数类型，例如 UInt64，但要确保所有类型都一致。

通过将所有整数类型统一为同一种类型，您可以避免 'NO_COMMON_TYPE' 错误并确保 ClickHouse 能够正确执行您的 SQL 查询。