Squeal.PostgreSQL.Expression.Window

Window functions and definitions

functions

partitionBy

Arguments

:: SListI bys

=> NP (Expression outer commons grp schemas params from) bys

partitions

-> WindowDefinition outer commons grp schemas params from

The partitionBy clause within Over divides the rows into groups, or partitions, that share the same values of the partitionBy Expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.

rank :: WinFun0 (NotNull PGint8)

rank of the current row with gaps; same as rowNumber of its first peer

>>> printSQL rank
rank()

rowNumber :: WinFun0 (NotNull PGint8)

number of the current row within its partition, counting from 1

>>> printSQL rowNumber
row_number()

denseRank :: WinFun0 (NotNull PGint8)

rank of the current row without gaps; this function counts peer groups

>>> printSQL denseRank
dense_rank()

percentRank :: WinFun0 (NotNull PGfloat8)

relative rank of the current row: (rank - 1) / (total partition rows - 1)

>>> printSQL percentRank
percent_rank()

cumeDist :: WinFun0 (NotNull PGfloat8)

cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows

>>> printSQL cumeDist
cume_dist()

ntile :: WinFun1 (NotNull PGint4) (NotNull PGint4)

integer ranging from 1 to the argument value, dividing the partition as equally as possible

>>> printSQL $ ntile 5
ntile(5)

lag :: WinFunN '[ty, NotNull PGint4, ty] ty

returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row.

lead :: WinFunN '[ty, NotNull PGint4, ty] ty

returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row.

firstValue :: WinFun1 ty ty

returns value evaluated at the row that is the first row of the window frame

lastValue :: WinFun1 ty ty

returns value evaluated at the row that is the last row of the window frame

nthValue :: WinFunN '[null ty, NotNull PGint4] (Null ty)

returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row

unsafeWindowFunction1 :: ByteString -> WinFun1 x y

escape hatch for defining window functions

unsafeWindowFunctionN :: SListI xs => ByteString -> WinFunN xs y

escape hatch for defining multi-argument window functions

types

newtype WindowFunction (outer :: FromType) (commons :: FromType) (grp :: Grouping) (schemas :: SchemasType) (params :: [NullityType]) (from :: FromType) (ty :: NullityType)

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

Constructors

UnsafeWindowFunction

Fields

• renderWindowFunction :: ByteString

Instances

Aggregate (Expression outer commons grp schemas params from) (NP (Expression outer commons grp schemas params from) :: [NullityType] -> Type) (WindowFunction outer commons grp schemas params from)

Defined in Squeal.PostgreSQL.Expression.Window

Methods

countStar :: WindowFunction outer commons grp schemas params from (NotNull PGint8)

count :: Expression outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from (NotNull PGint8)

sum_ :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null (PGSum ty))

arrayAgg :: Expression outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from (Null (PGvararray ty))

jsonAgg :: Expression outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from (Null PGjson)

jsonbAgg :: Expression outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from (Null PGjsonb)

bitAnd :: In int PGIntegral => Expression outer commons grp schemas params from (null int) -> WindowFunction outer commons grp schemas params from (Null int)

bitOr :: In int PGIntegral => Expression outer commons grp schemas params from (null int) -> WindowFunction outer commons grp schemas params from (Null int)

boolAnd :: Expression outer commons grp schemas params from (null PGbool) -> WindowFunction outer commons grp schemas params from (Null PGbool)

boolOr :: Expression outer commons grp schemas params from (null PGbool) -> WindowFunction outer commons grp schemas params from (Null PGbool)

every :: Expression outer commons grp schemas params from (null PGbool) -> WindowFunction outer commons grp schemas params from (Null PGbool)

max_ :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null ty)

min_ :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null ty)

avg :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null (PGAvg ty))

corr :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

covarPop :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

covarSamp :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

regrAvgX :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

regrAvgY :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

regrCount :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGint8)

regrIntercept :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

regrR2 :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

regrSlope :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

regrSxx :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

regrSxy :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

regrSyy :: NP (Expression outer commons grp schemas params from) (null PGfloat8 ': (null PGfloat8 ': [])) -> WindowFunction outer commons grp schemas params from (Null PGfloat8)

stddev :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null (PGAvg ty))

stddevPop :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null (PGAvg ty))

stddevSamp :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null (PGAvg ty))

variance :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null (PGAvg ty))

varPop :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null (PGAvg ty))

varSamp :: Expression outer commons grp schemas params from (null ty) -> WindowFunction outer commons grp schemas params from (Null (PGAvg ty))

Eq (WindowFunction outer commons grp schemas params from ty)

Defined in Squeal.PostgreSQL.Expression.Window

Methods

(==) :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> Bool

(/=) :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> Bool

Ord (WindowFunction outer commons grp schemas params from ty)

Defined in Squeal.PostgreSQL.Expression.Window

Methods

compare :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> Ordering

(<) :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> Bool

(<=) :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> Bool

(>) :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> Bool

(>=) :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> Bool

max :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty

min :: WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty -> WindowFunction outer commons grp schemas params from ty

Show (WindowFunction outer commons grp schemas params from ty)

Defined in Squeal.PostgreSQL.Expression.Window

Methods

showsPrec :: Int -> WindowFunction outer commons grp schemas params from ty -> ShowS

show :: WindowFunction outer commons grp schemas params from ty -> String

showList :: [WindowFunction outer commons grp schemas params from ty] -> ShowS

Generic (WindowFunction outer commons grp schemas params from ty)

Defined in Squeal.PostgreSQL.Expression.Window

Associated Types

type Rep (WindowFunction outer commons grp schemas params from ty) :: Type -> Type

Methods

from :: WindowFunction outer commons grp schemas params from ty -> Rep (WindowFunction outer commons grp schemas params from ty) x

to :: Rep (WindowFunction outer commons grp schemas params from ty) x -> WindowFunction outer commons grp schemas params from ty

NFData (WindowFunction outer commons grp schemas params from ty)

Defined in Squeal.PostgreSQL.Expression.Window

Methods

rnf :: WindowFunction outer commons grp schemas params from ty -> ()

RenderSQL (WindowFunction outer commons grp schemas params from ty)

Defined in Squeal.PostgreSQL.Expression.Window

Methods

renderSQL :: WindowFunction outer commons grp schemas params from ty -> ByteString

type Rep (WindowFunction outer commons grp schemas params from ty)

Defined in Squeal.PostgreSQL.Expression.Window

type Rep (WindowFunction outer commons grp schemas params from ty) = D1 (MetaData "WindowFunction" "Squeal.PostgreSQL.Expression.Window" "squeal-postgresql-0.5.2.0-4fAomBtpMjd6mRwLthA4w2" True) (C1 (MetaCons "UnsafeWindowFunction" PrefixI True) (S1 (MetaSel (Just "renderWindowFunction") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 ByteString)))

data WindowDefinition outer commons grp schemas params from where

A WindowDefinition is a set of table rows that are somehow related to the current row

Constructors

WindowDefinition

Fields

• :: SListI bys
• => NP (Expression outer commons grp schemas params from) bys

  partitionBy clause

• -> [SortExpression outer commons grp schemas params from]

  orderBy clause

• -> WindowDefinition outer commons grp schemas params from

Instances

OrderBy WindowDefinition

Defined in Squeal.PostgreSQL.Expression.Window

Methods

orderBy :: [SortExpression outer commons grp schemas params from] -> WindowDefinition outer commons grp schemas params from -> WindowDefinition outer commons grp schemas params from

RenderSQL (WindowDefinition outer commons schemas from grp params)

Defined in Squeal.PostgreSQL.Expression.Window

Methods

renderSQL :: WindowDefinition outer commons schemas from grp params -> ByteString

type WinFun0 x

Arguments

 = WindowFunction outer commons grp schemas params from x

cannot reference aliases

A RankNType for window functions with no arguments.

type WinFun1 x y

Arguments

 = Expression outer commons grp schemas params from x

input

-> WindowFunction outer commons grp schemas params from y

output

A RankNType for window functions with 1 argument.

type WinFunN xs y

Arguments

 = NP (Expression outer commons grp schemas params from) xs

inputs

-> WindowFunction outer commons grp schemas params from y

output

A RankNType for window functions with a fixed-length list of heterogeneous arguments. Use the *: operator to end your argument lists.