Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8091

Simple window functions

    XMLWordPrintable

Details

    • 10.2.0-5, 10.2.0-6, 10.2.0-7, 10.2.0-8, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-4

    Description

      There is a class of window functions that can be computed on the fly, after ordering. These functions are:

      • rank (DONE)
      • dense_rank (DONE)
      • row_number (DONE)
      • first_value (this is frame-based) (DONE)

      These functions can be computed directly. In order to do this we must:

      1. Sort the rows.
      2. Detect partition boundaries (on the fly as well)
      3. Given partition boundaries, compute the corresponding function value, for each row.

      Two-pass window functions:

      • percent_rank (test implementation needs review)
      • cume_dist (see MDEV-9746)
      • ntile
      • nth_value (this is frame-based) (DONE)
      • last_value (this is frame-based) (DONE)
        these require two passes over partition to compute. The extra information that we require is the number of rows in the partition. In order to find the number of rows, we must first detect partition boundaries and then we can compute the number of rows per partition.

      Two-cursor window functions:

      • lag (DONE)
      • lead (DONE)
        these require an additional cursor that is traveling n rows ahead/behind the current_row cursor.

      Key pieces for implementing this:

      • Make use of the filesort interface, since we do not need temporary tables for these functions.
      • It is a very similar use case to the GROUP BY statement. An important task is figuring out partition boundaries. The classes used for computing GROUP BY, might prove useful.

      Attachments

        Issue Links

          Activity

            People

              cvicentiu Vicențiu Ciorbaru
              cvicentiu Vicențiu Ciorbaru
              Votes:
              9 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.