Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-23899 Built-in SQL Function Improvement
  3. SPARK-23911

High-order function: aggregate(array<T>, initialState S, inputFunction<S, T, S>, outputFunction<S, R>) → R

    XMLWordPrintableJSON

    Details

    • Type: Sub-task
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.0
    • Fix Version/s: 2.4.0
    • Component/s: SQL
    • Labels:
      None

      Description

      Ref: https://prestodb.io/docs/current/functions/array.html

      Returns a single value reduced from array. inputFunction will be invoked for each element in array in order. In addition to taking the element, inputFunction takes the current state, initially initialState, and returns the new state. outputFunction will be invoked to turn the final state into the result value. It may be the identity function (i -> i).

      SELECT aggregate(ARRAY [], 0, (s, x) -> s + x, s -> s); -- 0
      SELECT aggregate(ARRAY [5, 20, 50], 0, (s, x) -> s + x, s -> s); -- 75
      SELECT aggregate(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + x, s -> s); -- NULL
      SELECT aggregate(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + COALESCE(x, 0), s -> s); -- 75
      SELECT aggregate(ARRAY [5, 20, NULL, 50], 0, (s, x) -> IF(x IS NULL, s, s + x), s -> s); -- 75
      SELECT aggregate(ARRAY [2147483647, 1], CAST (0 AS BIGINT), (s, x) -> s + x, s -> s); -- 2147483648
      SELECT aggregate(ARRAY [5, 6, 10, 20], -- calculates arithmetic average: 10.25
                    CAST(ROW(0.0, 0) AS ROW(sum DOUBLE, count INTEGER)),
                    (s, x) -> CAST(ROW(x + s.sum, s.count + 1) AS ROW(sum DOUBLE, count INTEGER)),
                    s -> IF(s.count = 0, NULL, s.sum / s.count));
      

        Attachments

          Activity

            People

            • Assignee:
              ueshin Takuya Ueshin
              Reporter:
              smilegator Xiao Li
            • Votes:
              1 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: