Session 5 - Aggregate Functions, spatial aggregates Flashcards
List all window Functions
CUME_DIST DENSE_RANK FIRST_VALUE LAG LAST_VALUE LEAD NTILE PERCENT_RANK RANK ROW_NUMBER
CUME_DIST Function
Calculate the cumulative distribution of a value in a set of values
DENSE_RANK Function
Assign a rank value to each row within a partition of a result, with no gaps in rank values.
FIRST_VALUE Function
Get the value of the first row in an ordered partition of a result set.
LAG Function
Provide access to a row at a given physical offset that comes before the current row.
LAST_VALUE Function
Get the value of the last row in an ordered partition of a result set.
LEAD Function
Provide access to a row at a given physical offset that follows the current row.
NTILE Function
Distribute rows of an ordered partition into a number of groups or buckets
PERCENT_RANK Function
Calculate the percent rank of a value in a set of values.
RANK Function
Assign a rank value to each row within a partition of a result set
ROW_NUMBER Function
Assign a unique sequential integer to rows within a partition of a result set, the first row starts from 1.
Aggregate Over Syntax
AGGREGATE() OVER (PARTITION BY COLUMN1, COLUMN2 ORDER BY COLUMN1, COLUMN2 DESC)
what happens to the total when you have an order by and a sum (pertaining to Over clause)
the total is incremented
What is the syntax using ROWS in Over Clause
ROWS BETWEEN [NUMBER] PRECEDING AND [NUMBER] FOLLOWING
UNBOUNDED AND CURRENT ROW (Instead of Preceding or Following) Can also be used instead of number
What is the syntax using RANGE in Over Clause
RANGE BETWEEN [UNBOUNDED or CURRENT ROW or 0] PRECEDING AND [UNBOUNDED or CURRENT ROW or 0] FOLLOWING
Which is more efficient, ROW or RANGE?
ROW. Range has to worry about ties and is overall slower
What is required with ROW and RANGE
ORDER BY.
What is default when using OVER (Regarding ROWS and RANGE) Without ORDER BY? With ORDER BY?
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING IS DEFAULT
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IS DEFAULT WHERE THERE IS ORDER BY
What do ROW_NUMBER(), RANK(), DENSE_RANK() OVER () require?
ORDER BY
How do you use ROW_NUMBER() without specifying a column to order by?
ROW_NUMBER OVER (ORDER BY (SELECT NULL))
This will be faster because it doesn’t have to order your data
How do you define multiple grouping sets?
Use ROLLUP within Group By clause:
SELECT COL1, COL2, COL3, SUM(COL4)
FROM TABLE
GROUP BY ROLLUP(COL1, COL2, COL3)
THIS WILL SUM COL1 THEN SHOW NULL, THEN COL1 AND COL2 WITH TOTAL SHOWING NULLS, THEN ALL THREE AND SO ON.
How can you tell if a column is grouped in a ROLLUP statement?
Use GROUPING(COLUMN) as ‘ALIAS’
This will show 1 if it is grouped or 0 if not.
How can you determine which columns are being grouped in a ROLLUP statement?
GROUPING_ID(COL1, COL2, COL3)
This will set columns in a binary order (COL1 - 1, COL2 - 2, COL3 - 4…) and will add the columns that are grouped (IE, if COL1 and COL3 were grouped in the row, the value would be 5)
What are all of the extensions of the group by clause?
ROLLUP
CUBE
GROUPING SETS
What is a GROUPING SET?
It allows you to specify which columns you want to rollup.
GROUP BY GROUPING SETS((COL1, COL2, COL3), (COL2, COL3),(COL1),())
() - Means ALL columns
What data is returned when using CUBE within the GROUP BY CLAUSE?
Every possible combination of group by will be performed:
SELECT COL1, COL2, COL3
FROM TABLE
GROUP BY CUBE
SUM(COL1), COL2, COL3 SUM(COL1), SUM(COL2), COL3 SUM(COL1), SUM(COL3), COL2 SUM(COL2), COL1, COL3 SUM(COL2), SUM(COL3), COL1 SUM(COL3), COL1, COL2 SUM(COL1),SUM(COL2),SUM(COL3)
How can you order grouped sums with nulls at bottom?
ORDER BY
CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END, COL1,
CASE WHEN COL2 IS NULL THEN 1 ELSE 0 END, COL2,
What is Geometry data type used for?
Place points of data on a graph
How would you place point 3,4 on a Geometry data point?
geometry::STGeomFromText(‘POINT (3 4)’, 0)
Name and describe all Geography Aggregate Methods
CollectionAggregate
Combines all of the shapes, but keeps all of the shapes as their own shape, but all on a single row
ConvexHullAggregate
creates a complex shape around the entirety of the shapes
EnvelopeAggregate
Creates a straight line shape around all of the shapes
UnionAggregate
Aggregate together shapes which puts all points and shapes into a single shape, so any line that goes through another shape is merged into the original
What are PERCENTILE_CONT(PARAM) and PERCENTILE_DISC(PARAM)?
PERCENTILE_CONT(PARAM) Finds the value closest to the parameter in a continuous line (one that doesn’t exist in the data per se
PERCENTILE_DIST(PARAM) Finds the value closest to the parameter in a continuous line that DOES exist in the data
What is the syntax of PERCENTILE_CONT(PARAM) and PERCENTILE_DISC(PARAM)?
PERCENTILE_CONTDIST WITHIN GROUP (ORDER BY order_by_expression) OVER (partition_by_clause)
How do you create a geometry point?
use:
geometry::STGeomFromText(‘POINT (X Y)’, 0)
OR
geometry::Point(x, y, 0)
How do you create multiple points in a single geometry statement?
geometry::STGeomFromText(‘MULTIPOINT ((X Y), (X Y), (X Y)), 0)
What are the Methods on Geometry Instances?
STArea STAsBinary STAsText STBoundary STBuffer STCentroid STContains STConvexHull STCrosses STCurveN STCurveToLine STDifference STDimension STDisjoint STDistance STEndpoint STEnvelope STEquals STExteriorRing STGeometryN STGeometryType STInteriorRingN STIntersection STIntersects STIsClosed STIsEmpty STIsRing STIsSimple STIsValid STLength STNumCurves STNumGeometries STNumInteriorRing STNumPoints STOverlaps STPointN STPointOnSurface STRelate STSrid STStartPoint STSymDifference STTouches STUnion STWithin STX STY
How do you create a geometry line?
geometry::STGeomFromText(‘LINESTRING (X Y, X Y, X Y…)’, 0)
How do you create multiple geometry lines in the same statement?
geometry::STGeomFromText(‘MULTILINESTRING((X Y, X Y…), (X Y, X Y…)), 0)
How do you create a geometry Polygon?
geometry::STGeomFromText(‘POLYGON ((X Y, X Y…))’, 0)
The FIRST and LAST coordinates MUST be the same
How do you create a geometry Circle?
geometry::STGeomFromText(‘CIRCULARSTRING(X Y, X Y…)’’, 0)
The FIRST and LAST coordinates MUST be the same
How do you find the intersection of two objects?
CurrentRow.STIntersection(@PreviousObject)
How do you find the shortest distance between two objects?
CurrentRow.STDistance(@PreviousObject)
How do you combine two objects into a single row?
CurrentRow.STUnion(@PreviousObject)
How do you set a point using Geography?
geography::STGeomFromText(‘POINT (LONG LAT)’, 4326)
How can you filter based on objects that intersect an object?
WHERE CURRENTGEOMETRY.Filter(geometry::Parse(‘POLYGON ((X Y)…)’)) = 1
This creates a filter based on the polygon that you pass to it. 1 means it will intersect with it.
How do you combine all shapes into a single shape?
geometry::UnionAggregate(GeometryObject)
ie:
declare @I as geometry
SELECT @I = geometry::UnionAggregate(GXY)
FROM DBO.GeometryTable
How do you combine all shapes while keeping each shape as its own shape?
geometry::CollectionAggregate(GeometryObject)
ie:
declare @I as geometry
SELECT @I = geometry::CollectionAggregate(GXY)
FROM DBO.GeometryTable
How do you combine shapes by putting a box around all of the shapes to combine?
geometry::EnvelopeAggregate(GeometryObject)
ie:
declare @I as geometry
SELECT @I = geometry::EnvelopeAggregate(GXY)
FROM DBO.GeometryTable
How do you combine shapes by putting a new shape as close around the shapes as possible?
geometry::ConvexHullAggregate(GeometryObject)
ie:
declare @I as geometry
SELECT @I = geometry::ConvexHullAggregate(GXY)
FROM DBO.GeometryTable
