查询中的数字表达式返回的数据类型

Question

在cloudera的Impala指南( functions.html)中，有一个示例演示如何使用type ()函数检查数字表达式的返回数据类型：

+--------------------------+
| typeof(5.30001 / 2342.1) |
+--------------------------+
| DECIMAL(13,11)           |
+--------------------------+

当我这么做时：

select typeof(5.30001),typeof(2342.1),typeof(5.30001 / 2342.1);

它给出的是这样的东西

DECIMAL(6,5)    DECIMAL(5,1)    DECIMAL(13,11)

我的意思是前两个是显而易见的，但我不知道为什么第三个数据类型类似于that.Can，返回的数据类型是从数字表达式本身确定的吗？此外，对于列除法表达式，我表示为decimal(13,5)/decimal(25,4)(例如)是否有方法确定返回的数据类型应该是什么？谢谢。

Answer 1

这确实有点模糊。下面是执行算术十进制类型转换的相关代码。

  /**
   * Returns the result type for (t1 op t2) where t1 and t2 are both DECIMAL, used when
   * DECIMAL version 2 is enabled.
   *
   * These rules are similar to (post Dec 2016) Hive / sql server rules.
   * http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/29/564110.aspx
   * https://msdn.microsoft.com/en-us/library/ms190476.aspx
   *
   * TODO: implement V2 rules for ADD/SUB.
   *
   * Changes:
   *  - There are slight difference with how precision/scale reduction occurs compared
   *    to SQL server when the desired precision is more than the maximum supported
   *    precision.  But an algorithm of reducing scale to a minimum of 6 is used.
   */
  private static ScalarType getDecimalArithmeticResultTypeV2(Type t1, Type t2,
      ArithmeticExpr.Operator op) throws AnalysisException {
    Preconditions.checkState(t1.isFullySpecifiedDecimal());
    Preconditions.checkState(t2.isFullySpecifiedDecimal());
    ScalarType st1 = (ScalarType) t1;
    ScalarType st2 = (ScalarType) t2;
    int s1 = st1.decimalScale();
    int s2 = st2.decimalScale();
    int p1 = st1.decimalPrecision();
    int p2 = st2.decimalPrecision();
    int resultScale;
    int resultPrecision;

    switch (op) {
      case DIVIDE:
        // Divide result always gets at least MIN_ADJUSTED_SCALE decimal places.
        resultScale = Math.max(ScalarType.MIN_ADJUSTED_SCALE, s1 + p2 + 1);
        resultPrecision = p1 - s1 + s2 + resultScale;
        break;
      case MOD:
        resultScale = Math.max(s1, s2);
        resultPrecision = Math.min(p1 - s1, p2 - s2) + resultScale;
        break;
      case MULTIPLY:
        resultScale = s1 + s2;
        resultPrecision = p1 + p2 + 1;
        break;
      case ADD:
      case SUBTRACT:
      default:
        // Not yet implemented - fall back to V1 rules.
        return getDecimalArithmeticResultTypeV1(t1, t2, op);
    }
    // Use the scale reduction technique when resultPrecision is too large.
    return ScalarType.createAdjustedDecimalType(resultPrecision, resultScale);
  }

s1 = 5和p2 = 5，输出比例尺为s1 + p2 + 1 == 11，精度为6-5+1+ 11 == 13。这些规则在评论链接中得到了更好的解释。