Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

T-SQL Transaction > Chapter 5 Grouping and Windowing > Flashcards

Chapter 5 Grouping and Windowing Flashcards

1
Q

What is a window function?

A

You define a set of rows per function and then return one result value per each underlying row and function. The window is defined with respect to the current row.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What does COUNT(col1) return when all inputs are NULLs?

A

It returns 0 when all inputs are NULLs whereas other general set functions like MIN, MAX, SUM, and AVG return NULL in such as case.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the general form for a pivot query?

A

WITH PivotData AS ( SELECT < grouping column >, < spreading column >, < aggregation column > FROM < source table > ) SELECT < select list > FROM PivotData PIVOT ( < aggregate function >(< aggregation column >) FOR < spreading column > IN () ) as P;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What clause do you use to define a window?

A

You use an OVER clause to define a window. When using empty parenthesis, the OVER clause represents the entire underlying query’s result set, e.g. SUM(val) OVER () represents the grand total sum over all rows - it’s treated as one partition. You can use a window function partition clause to restrict the window, e.g. SUM(val) OVER (PARTITION BY custid) represents the current customer’s total.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Which window option ROWS or RANGE gets optimized better?

A

The ROWS option usually gets optimized much better than RANGE when using the same delimiters.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

When using general set functions, how can you work with distinct occurrences?

A

You can specify a DISTINCT clause before the expression, e.g. COUNT(DISTINCT shippeddate).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are the clauses that window functions support?

A

Partitioning, ordering, and framing clauses.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What happens when you invoke a group function, but don’t include an explicit GROUP BY clause?

A

All rows are arranged in one group and then the group function operates on the rows in that single group.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is a group function?

A

You use grouped queries to arrange the queried rows in groups and then the group functions are applied to each group. You get one result row per group - not per underlying row.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are the three types of window functions?

A

Aggregate, ranking, and offset.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Does the UNPIVOT operator filter out rows with NULLs in the value column?

A

Yes. The assumption is that those represent inapplicable cases. There’s no reason to keep a row for a certain customer-shipper pair if it’s not applicable.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What are the steps to a pivot query?

A

(1) Define a table expression (like the one named PivotData) that returns the 3 elements necessary for pivoting. (2) Issue an outer query against the table expression and apply the PIVOT operator to that table expression. The PIVOT operator returns a table result. Assign an alias to the table result (P). (3) Specify the aggregate function for the PIVOT operator (e.g. SUM) (4) Then specify the FOR clause followed by the spreading column. (5) Then specify the IN clause followed by the list of distinct values that appear in the spreading element, separated by commas.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the default when you define a window with a window order clause, but no window frame clause?

A

The default is *RANGE* BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Therefore, if you are after the special behavior you get from RANGE that includes peers, make sure you explicitly define the ROWS option.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the 4 different window offset functions available in T-SQL?

A

LAG, LEAD, FIRST_VALUE, and LAST_VALUE.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What are the clauses that you can use to define multiple grouping sets in the same query?

A

GROUPING SETS, CUBE, and ROLLUP.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are the general set functions supported by SQL?

A

(1) COUNT, (2) SUM, (3) AVG, (4) MIN, and (5) MAX. General set functions are applied to an expression and ignore NULLs.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

When does a query become a “grouped query”?

A

When you use an aggregate function, a GROUP BY clause, or both.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the difference between COUNT(col1) and COUNT(*)?

A

The former ignores NULLs and therefore the counts are less than or equal to those produced by the latter.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are the differences between ROWS and RANGE window frame extent?

A

RANGE is based on logical offsets from the current row’s sort key. ROWS is based on physical offsets in terms of number of rows from the current row. SQL 2012 has a very limited implementation of RANGE and supports only UNBOUNDED PRECEDING or FOLLOWING and CURRENT ROW as delimiters. One difference between ROWS and RANGE when using the same delimiters is that the former doesn’t include tied rows in terms of the sort key and the latter does.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is the difference between the HAVING and WHERE clauses?

A

WHERE is evaluated per row; HAVING is evaluated per group.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

How can you tell whether a NULL in the grouped results represents a placeholder or an original NULL from the table?

A

T-SQL provides 2 functions: GROUPING and GROUPING_ID.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is pivoting data?

A

Pivoting is a technique that groups and aggregates data, transitioning it from a state of rows to a state of columns.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What frame extent clause would you use to include only the last three rows?

A

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Can you specify multiple GROUPING SETS, CUBE, and ROLLUP clauses in the GROUP BY clause?

A

Yes; however, by doing so, you achieve a multiplication effect. For example, CUBE(a,b,c) yields 8 grouping sets. ROLLUP(x,y,z) defines 4 grouping sets. By specifying a column between them as in CUBE(a,b,c), ROLLUP(x,y,z) you multiply them and get 32 grouping sets.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What is the CUBE clause and how do you use it?

A

The CUBE clause accepts a list of expressions as inputs and defines all possible grouping sets that can be generated from the inputs - including the empty grouping set., e.g. GROUP BY CUBE (shipperid, YEAR(shippeddate)); This produces 4 grouping sets: (1) shipperid, (2) YEAR(shippeddate), (3) shipperid, YEAR(shippeddate), (4) (Empty)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is the NTILE function?

A

The NTILE function allows you to arrange the rows within the partition into a requested number of equally sized tiles based on the specified ordering. You specify the desired number of tiles as input to the function, e.g. NTILE(100). If there are 830 rows in the result set, the tile size is 830 / 100 = 8 with a rem of 30. Because there is a rem, the first 30 tiles are assigned an extra row.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Why should you prepare a table expression for the pivot operator to return only the three elements needed for the pivot task?

A

Because all elements besides the aggregation and spreading elements are implicitly used for grouping. By using a table expression, you control which columns are used for grouping.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What is the difference between pivot and unpivot?

A

Pivot rotates data from a state of rows to a state of column headers.

Unpivot rotates the data from a state of column headers to a state of row values.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What are the LAG and LEAD functions?

A

The LAG function returns an element from the row in the current partition that is a requested number of rows *before* the current row with 1 assumed as the default offset. The LEAD function returns an element from the row that is in the requested offset *after* the current row.

If no explicit offset is specified, it uses a default of 1. If you want a different offset, you specify it as the second argument,

LAG(val,3)

If a row doesn’t exist in the requested offset, NULL is returned. To return something different, specify it as the third argument.

LAG(val,3,0).

The LAG and LEAD functions support window partition and ordering clauses.

But, do not support a window frame clause.

30
Q

The aggregation and spreading elements are indicated in the PIVOT clause, but how is the grouping element identified?

A

Process of elimination - it’s what’s left from the queried table besides the aggregation and spreading elements.

31
Q

What is the ROLLUP clause and how do you use it?

A

The ROLLUP clause accepts a list of expressions as inputs and defines a hierarchy formed by the input elements such as a location hierarchy (country, region, city), .e.g GROUP BY ROLLUP (country, region, city) produces 4 grouping sets: (1) country, region, city, (2) country, region, (3) country, (4) (Empty).

32
Q

Is the ORDER BY clause mandatory with the window ranking functions?

A

Yes.But the window order clause only determines ordering for the window function’s computation not for presentation. There’s no guarantee that the rows will be presented in the same order as the window function’s ordering. If you need such a guarantee, add a presentation ORDER BY clause.

33
Q

What is unpivoting?

A

String with pivoted data, when unpivoting, you rotate the input data from a state of columns to a state of rows.

34
Q

When using grouping sets, what happens in the output when an element isn’t part of the grouping set?

A

NULLs are used as placeholders in rows where an element isn’t part of the grouping set.

35
Q

What are the four limitations to the PIVOT operator?

A

(1) The aggregation and spreading elements cannot be the results of expressions - they must be column names from the queried table. You can however apply expressions in the query defining the table expression, assign aliases to those expressions, and then use the aliases in PIVOT (2) The COUNT(*) function isn’t allowed as an aggregate function used by PIVOT. You must use COUNT( < col name >) There is a work around using the table expression (3) PIVOT is used to using only one aggregate function (4) The IN clause of the PIVOT operator accepts a static list of spreading values. It doesn’t support a subquery as input. You need to know ahead of time what the distinct values are in the spreading column. You can use dynamic SQL to work around this.

36
Q

In general, what purpose do the window offset functions serve?

A

Return an element from a single row that is in a given offset from the current row in the window partition, or from the first or last row in the window frame.

37
Q

What is an example of LAG/LEAD?

A

SELECT

custid,

orderid,

orderdate,

val,

LAG/LEAD(val)

OVER

(

PARTITION BY custid

ORDER BY orderdate, orderid

)

FROM Sales.OrderValues

38
Q

What do the delimiters UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING represent?

A

The beginning and the end of the partition.

39
Q

When using a GROUP BY clause, what must be done to expressions in the clauses that follow, namely, HAVING, SELECT, and ORDER BY?

A

All expressions that appear in those clauses must guarantee a single result value per group. There’s no problem referring directly to elements that appear in the GROUP BY clause because each of those already return one distinct value per group. For other elements from the underlying table, you must apply an aggregate function.

40
Q

What is the RANK function?

A

The RANK function returns the number of rows that have a lower ordering value than the current plus 1. Can have gaps between ranking values.

41
Q

What is an example of unpivot?

A

SELECT custid, shipperid, freight FROM Sales.FreightTotals UNPIVOT(freight FOR shipperid IN ([1],[2],[3])) AS U;

42
Q

What is the general form for unpivot?

A

SELECT

< column list >,

< names column >,

< values column >

FROM < source table >

UNPIVOT

(

< values column > FOR < names column > IN ( )

) AS U;

43
Q

What is an example of FIRST_VALUE and LAST_VALUE?

A

SELECT

custid,

orderid,

orderdate,

value,

FIRST_VALUE(value)

OVER

(

PARTITION BY custid

ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) as first_val

44
Q

Can the GROUPING and GROUPING_ID functions be used to sort data based on grouping set association - that is, first detail and then aggregates?

A

These functions can be used for sorting data because they return a 0 bit for a detail element and a 1 bit for an aggregated element. So, if you want to see detail first, sort by the result of the function in ascending order.

45
Q

What are the FIRST_VALUE and LAST_VALUE functions?

A

The FIRST_VALUE and LAST_VALUE functions return a value expression from the first or last rows in the window frame respectively. These functions support both window partition, order, and frame clauses.

46
Q

Why are window functions allowed only in the SELECT and ORDER BY clauses of a query?

A

They are supported to operate on the underlying query’s result which is achieved when logical query processing gets to the SELECT phase.

47
Q

What is an example of the pivot query?

A

WITH PivotData AS ( SELECT custid, – grouping column shipperid, – spreading column freight, – aggregation column FROM Sales.Orders ) SELECT custid, [1], [2], [3] FROM PivotData PIVOT(SUM(freight) FOR shipperid IN ([1], [2], [3])) AS P;

48
Q

For performance reasons, is it generally recommended to avoid the RANGE option?

A

Yes. You need to be explicit and use the ROWS clause.

49
Q

What are the types of the names and values columns?

A

The names colum is defined as a nvarchar(128) and the values column is defined with the same type as the type of the source columns that were unpivoted.

50
Q

Can a grouping set be empty?

A

Yes. This results in one group with all rows for computation of grand aggregates.

51
Q

What is framing?

A

Framing is a filtering option available to window aggregate functions. You define ordering within the partition by using a window order clause, and then based on that order you can confine a frame of rows between two delimiters.

52
Q

What is the GROUPING function and how does it work?

A

Tells whether a NULL in the grouped results represents a placeholder or an original NULL. GROUPING accepts a single element as input and returns 0 when the element is part of the grouping set and 1 when it isn’t, e.g. GROUPING(country) => 0/1

53
Q

What is the HAVING clause?

A

The HAVING clause uses a predicate but evaluates the predicate per group as opposed to per row. This means that you can refer to aggregate computations because the data has already been grouped.

54
Q

What are the options for window framing units?

A

ROWS or RANGE

55
Q

What is the ROW_NUMBER function?

A

The ROW_NUMBER function computes a unique sequential integer starting with 1 within the window partition based on the window ordering. Note that if the ordering isn’t unique, the ROW_NUMBER function is not deterministic. If there’s no “tie breaker” in the ordering, the choice of which row gets the higher number is arbitrary - optimization dependent.

56
Q

What is the shorter form of the frame extent: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW?

A

ROWS UNBOUNDED PRECEDING

57
Q

What is the GROUPING_ID function and how does it work?

A

GROUPING_ID accepts the list of grouped columns as inputs and returns an integer representing a bitmap. The rightmost bit represents the rightmost input. The bit is 0 when the respective element is part of the grouping set and 1 when it isn’t. The result integer is the sum of the values representing elements that are not part of the grouping set because their bits are turned on., e.g. GROUPING_ID(country, region, city), 7 would represent the empty grouping set - none of the 3 elements is part of the grouping set. Therefore, the respective bits (1, 2, 4 => 7) are turned on.

58
Q

What frame do you need to specify if you’re after the FIRST_VALUE?

A

ROWS BETWEEN *UNBOUNDED PRECEDING* AND CURRENT ROW - you need the first row in the partition.

59
Q

What is the DENSE_RANK function?

A

The DENSE_RANK function returns the number of *distinct* ordering values that are lower than the current plus 1. Cannot have gaps between ranking values.

60
Q

What is one benefit of window functions over group functions?

A

Windowed queries do not hide detail - they return a row for every underlying query’s row. This means you can mix detail and aggregated elements in the same query.

61
Q

What are the three elements in every unpivoting task?

A

(1) The set of source columns that you’re unpivoting, (2) The name you want to assign to the target values column (e.g. “freight”), (3) The name you want to assign to the target names column (“shipperid”).

62
Q

What are the clauses that allow you to define multiple grouping sets?

A

(1) GROUPING SETS, (2) CUBE, (3) ROLLUP. You use these in the GROUP BY clause.

63
Q

What clauses are window functions allowed to be used?

A

SELECT and ORDER BY - if you need to refer to the result of a window function in any clause evaluated before SELECT, you need to use a table expression (CTE).

64
Q

What happens when you invoke a group function and include an explicit GROUP BY clause?

A

Rows are arrange in one or more groups according to the grouping set of expressions and the group function operates on each group.

65
Q

What frame do you need to specify if you’re after the LAST_VALUE?

A

ROWS BETWEEN CURRENT ROW AND *UNBOUNDED FOLLOWING* - you need the last row in the partition.

66
Q

What types of “functions” are PIVOT and UNPIVOT?

A

They are table operators similar to JOIN, etc.

67
Q

What are the delimiter options available for the ROWS window frame unit?

A

(1) UNBOUNDED PRECEDING or FOLLOWING, (2) CURRENT ROW, (3) < n > ROWS PRECEDING or FOLLOWING

68
Q

What is the GROUPING SETS clause and how do you use it?

A

You can use the GROUPING SETS clause to list all grouping sets that you want to define in the query. You list the grouping sets separated by commas within the outer pair of parenthesis. You use an inner pair of parenthesis to enclose each grouping set. If you don’t use inner parenthesis, each individual element is considered a separate grouping set. e.g. GROUP BY GROUPING SETS ( (shupperid, YEAR(shippeddate)), (shipperid), (YEAR(shippeddate)), ());

69
Q

What are the ranking functions supported in T-SQL?

A

ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

70
Q

What are the three things you need to identify in all pivot queries?

A

(1) What do you want to see on rows? This element is known as the “on rows” or “grouping element” (2) What do you want to see on columns? This element is known as the “on cols” or “spreading element” (3) What do you want to see in the intersection of each distinct row and column value? This element is known as the “data” or “aggregation element”.

71
Q

What is a data analysis function?

A

A data analysis function is a function applied to a set of rows and it returns a single value, e.g. the SUM aggregate function.

T-SQL Transaction (14 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions