Can I use… over(partition by …)

The over accepts the optional partition by clause to limit the scope of the window function to those rows that have the “same” values in the listed expressions.

SELECT *, COUNT(*) OVER(PARTITION BY c1, c2)
  FROM …

Returns the original result with an additional column stating how many rows the result has with the “same“ values in c1 and c2 as the current row.

In this context, “same” means is not distinct from, which treats two null values as indistinguishable (very much like group by does).

Related

• Sub clauses:

  • over(order by …)

  • Framing:

    • over(… rows between …)

    • over(… range between …) (numeric), over(… range between …) (datetime)

    • over(… groups between …)

    • Frame exclusion:

      • over(… exclude no others)

      • over(… exclude current row)

      • over(… exclude group)

      • over(… exclude ties)

• Product specific: over(range between…) (strings, multi-columns)

Normative References

The over clause, including partition by, is defined in ISO/IEC 9075:2016-2 as part of optional feature T611, “Elementary OLAP operations”.