Grouping#

An aggregate calculates one value for a group of rows. A window function calculates a value for each row from the rows around it. Both read the same five entities as Queries.

Aggregates#

An aggregate column in find is a list of the aggregate name, its argument, and the variable that holds the result. groupBy names the variables that form each group.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	4C 6D65616E	txt "mean"
009	4C 3F73616C	txt "?sal"
00E	5C 3F6D65616E	txt "?mean"
014	0E 15 00 0700 030000 00 04000000 090000 00 0E000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4 → @9 → @E
029	5C 3F64657074	txt "?dept"
02F	0E 11 00 0300 020000 00 29000000 140000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @29 → @14
040	2C 3F65	txt "?e"
043	4C 64657074	txt "dept"
048	5C 3F64657074	txt "?dept"
04E	0E 15 00 0700 030000 00 40000000 430000 00 48000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @40 → @43 → @48
063	2C 3F65	txt "?e"
066	6C 73616C617279	txt "salary"
06D	4C 3F73616C	txt "?sal"
072	0E 15 00 0700 030000 00 63000000 660000 00 6D000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @63 → @66 → @6D
087	0E 11 00 0300 020000 00 4E000000 720000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @4E → @72
098	5C 3F64657074	txt "?dept"
09E	0E 0D 00 0100 010000 00 98000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @98
0AB	5C 3F64657074	txt "?dept"
0B1	0E 0D 00 0100 010000 00 AB000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @AB
0BE	5C 7768657265	txt "where"
0C4	0F 0A BE000000 8700 0000	map leaf, size 10: @BE → @87
0CE	4C 66696E64	txt "find"
0D3	0F 0A CE000000 2F00 0000	map leaf, size 10: @CE → @2F
0DD	7C 6F726465724279	txt "orderBy"
0E5	0F 0A DD000000 B100 0000	map leaf, size 10: @DD → @B1
0EF	7C 67726F75704279	txt "groupBy"
0F7	0F 0A EF000000 9E00 0000	map leaf, size 10: @EF → @9E
101	07 16 2A040000 C400 0000 D3000000 E500 0000 F7000000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 10: @C4 → @D3 → @E5 → @F7
117	01010000 00000000	root → @101; previous: none

Offset (hex)	Bytes (hex)	Meaning
00	54 52 4F 4E	magic TRON
04	4C 6D65616E	txt "mean"
09	02 6E000000000000 00	i64 110
12	0F 0A 04000000 0900 0000	map leaf, size 10: @4 → @9
1C	4C 64657074	txt "dept"
21	3C 656E67	txt "eng"
25	0F 0A 1C000000 2100 0000	map leaf, size 10: @1C → @21
2F	07 0E 00110000 1200 0000 25000000	map branch, size 14, depth 0 (root), slots 8, 12: @12 → @25
3D	4C 6D65616E	txt "mean"
42	02 50000000000000 00	i64 80
4B	0F 0A 3D000000 4200 0000	map leaf, size 10: @3D → @42
55	4C 64657074	txt "dept"
5A	3C 6F7073	txt "ops"
5E	0F 0A 55000000 5A00 0000	map leaf, size 10: @55 → @5A
68	07 0E 00110000 4B00 0000 5E000000	map branch, size 14, depth 0 (root), slots 8, 12: @4B → @5E
76	4C 6D65616E	txt "mean"
7B	02 50000000000000 00	i64 80
84	0F 0A 76000000 7B00 0000	map leaf, size 10: @76 → @7B
8E	4C 64657074	txt "dept"
93	5C 73616C6573	txt "sales"
99	0F 0A 8E000000 9300 0000	map leaf, size 10: @8E → @93
A3	07 0E 00110000 8400 0000 99000000	map branch, size 14, depth 0 (root), slots 8, 12: @84 → @99
B1	0E 15 00 0700 030000 00 2F000000 680000 00 A3000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @2F → @68 → @A3
C6	B1000000 00000000	root → @B1; previous: none

One query can calculate several aggregates over the same group.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 636F756E74	txt "count"
00A	2C 3F65	txt "?e"
00D	2C 3F6E	txt "?n"
010	0E 15 00 0700 030000 00 04000000 0A0000 00 0D000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4 → @A → @D
025	3C 73756D	txt "sum"
029	4C 3F73616C	txt "?sal"
02E	6C 3F746F74616C	txt "?total"
035	0E 15 00 0700 030000 00 25000000 290000 00 2E000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @25 → @29 → @2E
04A	3C 6D696E	txt "min"
04E	4C 3F73616C	txt "?sal"
053	3C 3F6C6F	txt "?lo"
057	0E 15 00 0700 030000 00 4A000000 4E0000 00 53000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4A → @4E → @53
06C	3C 6D6178	txt "max"
070	4C 3F73616C	txt "?sal"
075	3C 3F6869	txt "?hi"
079	0E 15 00 0700 030000 00 6C000000 700000 00 75000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @6C → @70 → @75
08E	5C 3F64657074	txt "?dept"
094	0E 1D 00 1F00 050000 00 8E000000 100000 00 35000000 570000 00 79000000	array leaf, size 29, shift 0, bitmap 001F, count 5 (root): @8E → @10 → @35 → @57 → @79
0B1	2C 3F65	txt "?e"
0B4	4C 64657074	txt "dept"
0B9	5C 3F64657074	txt "?dept"
0BF	0E 15 00 0700 030000 00 B1000000 B40000 00 B9000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @B1 → @B4 → @B9
0D4	2C 3F65	txt "?e"
0D7	6C 73616C617279	txt "salary"
0DE	4C 3F73616C	txt "?sal"
0E3	0E 15 00 0700 030000 00 D4000000 D70000 00 DE000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @D4 → @D7 → @DE
0F8	0E 11 00 0300 020000 00 BF000000 E30000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @BF → @E3
109	5C 3F64657074	txt "?dept"
10F	0E 0D 00 0100 010000 00 09010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @109
11C	5C 3F64657074	txt "?dept"
122	0E 0D 00 0100 010000 00 1C010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @11C
12F	5C 7768657265	txt "where"
135	0F 0A 2F010000 F800 0000	map leaf, size 10: @12F → @F8
13F	4C 66696E64	txt "find"
144	0F 0A 3F010000 9400 0000	map leaf, size 10: @13F → @94
14E	7C 6F726465724279	txt "orderBy"
156	0F 0A 4E010000 2201 0000	map leaf, size 10: @14E → @122
160	7C 67726F75704279	txt "groupBy"
168	0F 0A 60010000 0F01 0000	map leaf, size 10: @160 → @10F
172	07 16 2A040000 3501 0000 44010000 5601 0000 68010000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 10: @135 → @144 → @156 → @168
188	72010000 00000000	root → @172; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	2C 6869	txt "hi"
007	02 78000000000000 00	i64 120
010	0F 0A 04000000 0700 0000	map leaf, size 10: @4 → @7
01A	2C 6C6F	txt "lo"
01D	02 64000000000000 00	i64 100
026	0F 0A 1A000000 1D00 0000	map leaf, size 10: @1A → @1D
030	5C 746F74616C	txt "total"
036	02 DC000000000000 00	i64 220
03F	0F 0A 30000000 3600 0000	map leaf, size 10: @30 → @36
049	4C 64657074	txt "dept"
04E	3C 656E67	txt "eng"
052	0F 0A 49000000 4E00 0000	map leaf, size 10: @49 → @4E
05C	1C 6E	txt "n"
05E	02 02000000000000 00	i64 2
067	0F 0A 5C000000 5E00 0000	map leaf, size 10: @5C → @5E
071	07 1A 50580000 1000 0000 26000000 3F00 0000 52000000 6700 0000	map branch, size 26, depth 0 (root), slots 4, 6, 11, 12, 14: @10 → @26 → @3F → @52 → @67
08B	2C 6869	txt "hi"
08E	02 50000000000000 00	i64 80
097	0F 0A 8B000000 8E00 0000	map leaf, size 10: @8B → @8E
0A1	2C 6C6F	txt "lo"
0A4	02 50000000000000 00	i64 80
0AD	0F 0A A1000000 A400 0000	map leaf, size 10: @A1 → @A4
0B7	5C 746F74616C	txt "total"
0BD	02 50000000000000 00	i64 80
0C6	0F 0A B7000000 BD00 0000	map leaf, size 10: @B7 → @BD
0D0	4C 64657074	txt "dept"
0D5	3C 6F7073	txt "ops"
0D9	0F 0A D0000000 D500 0000	map leaf, size 10: @D0 → @D5
0E3	1C 6E	txt "n"
0E5	02 01000000000000 00	i64 1
0EE	0F 0A E3000000 E500 0000	map leaf, size 10: @E3 → @E5
0F8	07 1A 50580000 9700 0000 AD000000 C600 0000 D9000000 EE00 0000	map branch, size 26, depth 0 (root), slots 4, 6, 11, 12, 14: @97 → @AD → @C6 → @D9 → @EE
112	2C 6869	txt "hi"
115	02 5A000000000000 00	i64 90
11E	0F 0A 12010000 1501 0000	map leaf, size 10: @112 → @115
128	2C 6C6F	txt "lo"
12B	02 46000000000000 00	i64 70
134	0F 0A 28010000 2B01 0000	map leaf, size 10: @128 → @12B
13E	5C 746F74616C	txt "total"
144	02 A0000000000000 00	i64 160
14D	0F 0A 3E010000 4401 0000	map leaf, size 10: @13E → @144
157	4C 64657074	txt "dept"
15C	5C 73616C6573	txt "sales"
162	0F 0A 57010000 5C01 0000	map leaf, size 10: @157 → @15C
16C	1C 6E	txt "n"
16E	02 02000000000000 00	i64 2
177	0F 0A 6C010000 6E01 0000	map leaf, size 10: @16C → @16E
181	07 1A 50580000 1E01 0000 34010000 4D01 0000 62010000 7701 0000	map branch, size 26, depth 0 (root), slots 4, 6, 11, 12, 14: @11E → @134 → @14D → @162 → @177
19B	0E 15 00 0700 030000 00 71000000 F80000 00 81010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @71 → @F8 → @181
1B0	9B010000 00000000	root → @19B; previous: none

An aggregate can also collect the values of a group.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	6C 6D656469616E	txt "median"
00B	4C 3F73616C	txt "?sal"
010	4C 3F6D6564	txt "?med"
015	0E 15 00 0700 030000 00 04000000 0B0000 00 10000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4 → @B → @10
02A	DC 636F756E744469 7374696E6374	txt "countDistinct"
038	4C 3F73616C	txt "?sal"
03D	6C 3F6B696E6473	txt "?kinds"
044	0E 15 00 0700 030000 00 2A000000 380000 00 3D000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @2A → @38 → @3D
059	4C 6C697374	txt "list"
05E	5C 3F6E616D65	txt "?name"
064	4C 3F77686F	txt "?who"
069	0E 15 00 0700 030000 00 59000000 5E0000 00 64000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @59 → @5E → @64
07E	5C 3F64657074	txt "?dept"
084	0E 19 00 0F00 040000 00 7E000000 150000 00 44000000 690000 00	array leaf, size 25, shift 0, bitmap 000F, count 4 (root): @7E → @15 → @44 → @69
09D	2C 3F65	txt "?e"
0A0	4C 64657074	txt "dept"
0A5	5C 3F64657074	txt "?dept"
0AB	0E 15 00 0700 030000 00 9D000000 A00000 00 A5000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @9D → @A0 → @A5
0C0	2C 3F65	txt "?e"
0C3	6C 73616C617279	txt "salary"
0CA	4C 3F73616C	txt "?sal"
0CF	0E 15 00 0700 030000 00 C0000000 C30000 00 CA000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @C0 → @C3 → @CA
0E4	2C 3F65	txt "?e"
0E7	4C 6E616D65	txt "name"
0EC	5C 3F6E616D65	txt "?name"
0F2	0E 15 00 0700 030000 00 E4000000 E70000 00 EC000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @E4 → @E7 → @EC
107	0E 15 00 0700 030000 00 AB000000 CF0000 00 F2000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @AB → @CF → @F2
11C	5C 3F64657074	txt "?dept"
122	0E 0D 00 0100 010000 00 1C010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @11C
12F	5C 3F64657074	txt "?dept"
135	0E 0D 00 0100 010000 00 2F010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @12F
142	5C 7768657265	txt "where"
148	0F 0A 42010000 0701 0000	map leaf, size 10: @142 → @107
152	4C 66696E64	txt "find"
157	0F 0A 52010000 8400 0000	map leaf, size 10: @152 → @84
161	7C 6F726465724279	txt "orderBy"
169	0F 0A 61010000 3501 0000	map leaf, size 10: @161 → @135
173	7C 67726F75704279	txt "groupBy"
17B	0F 0A 73010000 2201 0000	map leaf, size 10: @173 → @122
185	07 16 2A040000 4801 0000 57010000 6901 0000 7B010000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 10: @148 → @157 → @169 → @17B
19B	85010000 00000000	root → @185; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	3C 416461	txt "Ada"
008	3C 426F62	txt "Bob"
00C	0E 11 00 0300 020000 00 04000000 080000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @4 → @8
01D	3C 6D6564	txt "med"
021	02 6E000000000000 00	i64 110
02A	0F 0A 1D000000 2100 0000	map leaf, size 10: @1D → @21
034	3C 77686F	txt "who"
038	0F 0A 34000000 0C00 0000	map leaf, size 10: @34 → @C
042	5C 6B696E6473	txt "kinds"
048	02 02000000000000 00	i64 2
051	0F 0A 42000000 4800 0000	map leaf, size 10: @42 → @48
05B	4C 64657074	txt "dept"
060	3C 656E67	txt "eng"
064	0F 0A 5B000000 6000 0000	map leaf, size 10: @5B → @60
06E	07 16 60120000 2A00 0000 38000000 5100 0000 64000000	map branch, size 22, depth 0 (root), slots 5, 6, 9, 12: @2A → @38 → @51 → @64
084	3C 457665	txt "Eve"
088	0E 0D 00 0100 010000 00 84000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @84
095	3C 6D6564	txt "med"
099	02 50000000000000 00	i64 80
0A2	0F 0A 95000000 9900 0000	map leaf, size 10: @95 → @99
0AC	3C 77686F	txt "who"
0B0	0F 0A AC000000 8800 0000	map leaf, size 10: @AC → @88
0BA	5C 6B696E6473	txt "kinds"
0C0	02 01000000000000 00	i64 1
0C9	0F 0A BA000000 C000 0000	map leaf, size 10: @BA → @C0
0D3	4C 64657074	txt "dept"
0D8	3C 6F7073	txt "ops"
0DC	0F 0A D3000000 D800 0000	map leaf, size 10: @D3 → @D8
0E6	07 16 60120000 A200 0000 B0000000 C900 0000 DC000000	map branch, size 22, depth 0 (root), slots 5, 6, 9, 12: @A2 → @B0 → @C9 → @DC
0FC	2C 4379	txt "Cy"
0FF	3C 44616E	txt "Dan"
103	0E 11 00 0300 020000 00 FC000000 FF0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @FC → @FF
114	3C 6D6564	txt "med"
118	02 50000000000000 00	i64 80
121	0F 0A 14010000 1801 0000	map leaf, size 10: @114 → @118
12B	3C 77686F	txt "who"
12F	0F 0A 2B010000 0301 0000	map leaf, size 10: @12B → @103
139	5C 6B696E6473	txt "kinds"
13F	02 02000000000000 00	i64 2
148	0F 0A 39010000 3F01 0000	map leaf, size 10: @139 → @13F
152	4C 64657074	txt "dept"
157	5C 73616C6573	txt "sales"
15D	0F 0A 52010000 5701 0000	map leaf, size 10: @152 → @157
167	07 16 60120000 2101 0000 2F010000 4801 0000 5D010000	map branch, size 22, depth 0 (root), slots 5, 6, 9, 12: @121 → @12F → @148 → @15D
17D	0E 15 00 0700 030000 00 6E000000 E60000 00 67010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @6E → @E6 → @167
192	7D010000 00000000	root → @17D; previous: none

These are the aggregate names:

Each name is an aggregate only in find. The same name in a where predicate is an ordinary operator.

Having#

having filters the groups after Stardust calculates the aggregates. Each entry is a predicate over the group's values.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	4C 6D65616E	txt "mean"
009	4C 3F73616C	txt "?sal"
00E	5C 3F6D65616E	txt "?mean"
014	0E 15 00 0700 030000 00 04000000 090000 00 0E000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4 → @9 → @E
029	5C 3F64657074	txt "?dept"
02F	0E 11 00 0300 020000 00 29000000 140000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @29 → @14
040	2C 3F65	txt "?e"
043	4C 64657074	txt "dept"
048	5C 3F64657074	txt "?dept"
04E	0E 15 00 0700 030000 00 40000000 430000 00 48000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @40 → @43 → @48
063	2C 3F65	txt "?e"
066	6C 73616C617279	txt "salary"
06D	4C 3F73616C	txt "?sal"
072	0E 15 00 0700 030000 00 63000000 660000 00 6D000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @63 → @66 → @6D
087	0E 11 00 0300 020000 00 4E000000 720000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @4E → @72
098	5C 3F64657074	txt "?dept"
09E	0E 0D 00 0100 010000 00 98000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @98
0AB	1C 3E	txt ">"
0AD	5C 3F6D65616E	txt "?mean"
0B3	02 55000000000000 00	i64 85
0BC	0E 15 00 0700 030000 00 AB000000 AD0000 00 B3000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @AB → @AD → @B3
0D1	0E 0D 00 0100 010000 00 BC000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @BC
0DE	5C 3F64657074	txt "?dept"
0E4	0E 0D 00 0100 010000 00 DE000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @DE
0F1	5C 7768657265	txt "where"
0F7	0F 0A F1000000 8700 0000	map leaf, size 10: @F1 → @87
101	6C 686176696E67	txt "having"
108	0F 0A 01010000 D100 0000	map leaf, size 10: @101 → @D1
112	4C 66696E64	txt "find"
117	0F 0A 12010000 2F00 0000	map leaf, size 10: @112 → @2F
121	7C 6F726465724279	txt "orderBy"
129	0F 0A 21010000 E400 0000	map leaf, size 10: @121 → @E4
133	7C 67726F75704279	txt "groupBy"
13B	0F 0A 33010000 9E00 0000	map leaf, size 10: @133 → @9E
145	07 1A 2E040000 F700 0000 08010000 1701 0000 29010000 3B01 0000	map branch, size 26, depth 0 (root), slots 1, 2, 3, 5, 10: @F7 → @108 → @117 → @129 → @13B
15F	45010000 00000000	root → @145; previous: none

Offset (hex)	Bytes (hex)	Meaning
00	54 52 4F 4E	magic TRON
04	4C 6D65616E	txt "mean"
09	02 6E000000000000 00	i64 110
12	0F 0A 04000000 0900 0000	map leaf, size 10: @4 → @9
1C	4C 64657074	txt "dept"
21	3C 656E67	txt "eng"
25	0F 0A 1C000000 2100 0000	map leaf, size 10: @1C → @21
2F	07 0E 00110000 1200 0000 25000000	map branch, size 14, depth 0 (root), slots 8, 12: @12 → @25
3D	0E 0D 00 0100 010000 00 2F000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @2F
4A	3D000000 00000000	root → @3D; previous: none

A having entry must start with an operator. A fact-shaped entry is an error.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	4C 6D65616E	txt "mean"
009	4C 3F73616C	txt "?sal"
00E	5C 3F6D65616E	txt "?mean"
014	0E 15 00 0700 030000 00 04000000 090000 00 0E000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4 → @9 → @E
029	5C 3F64657074	txt "?dept"
02F	0E 11 00 0300 020000 00 29000000 140000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @29 → @14
040	2C 3F65	txt "?e"
043	4C 64657074	txt "dept"
048	5C 3F64657074	txt "?dept"
04E	0E 15 00 0700 030000 00 40000000 430000 00 48000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @40 → @43 → @48
063	2C 3F65	txt "?e"
066	6C 73616C617279	txt "salary"
06D	4C 3F73616C	txt "?sal"
072	0E 15 00 0700 030000 00 63000000 660000 00 6D000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @63 → @66 → @6D
087	0E 11 00 0300 020000 00 4E000000 720000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @4E → @72
098	5C 3F64657074	txt "?dept"
09E	0E 0D 00 0100 010000 00 98000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @98
0AB	2C 3F65	txt "?e"
0AE	4C 64657074	txt "dept"
0B3	3C 656E67	txt "eng"
0B7	0E 15 00 0700 030000 00 AB000000 AE0000 00 B3000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @AB → @AE → @B3
0CC	0E 0D 00 0100 010000 00 B7000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @B7
0D9	5C 7768657265	txt "where"
0DF	0F 0A D9000000 8700 0000	map leaf, size 10: @D9 → @87
0E9	6C 686176696E67	txt "having"
0F0	0F 0A E9000000 CC00 0000	map leaf, size 10: @E9 → @CC
0FA	4C 66696E64	txt "find"
0FF	0F 0A FA000000 2F00 0000	map leaf, size 10: @FA → @2F
109	7C 67726F75704279	txt "groupBy"
111	0F 0A 09010000 9E00 0000	map leaf, size 10: @109 → @9E
11B	07 16 0E040000 DF00 0000 F0000000 FF00 0000 11010000	map branch, size 22, depth 0 (root), slots 1, 2, 3, 10: @DF → @F0 → @FF → @111
131	1B010000 00000000	root → @11B; previous: none

groupBy accepts each variable once. A repeated variable is an error.

Offset (hex)	Bytes (hex)	Meaning
00	54 52 4F 4E	magic TRON
04	4C 6D65616E	txt "mean"
09	4C 3F73616C	txt "?sal"
0E	5C 3F6D65616E	txt "?mean"
14	0E 15 00 0700 030000 00 04000000 090000 00 0E000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4 → @9 → @E
29	5C 3F64657074	txt "?dept"
2F	0E 11 00 0300 020000 00 29000000 140000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @29 → @14
40	2C 3F65	txt "?e"
43	4C 64657074	txt "dept"
48	5C 3F64657074	txt "?dept"
4E	0E 15 00 0700 030000 00 40000000 430000 00 48000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @40 → @43 → @48
63	2C 3F65	txt "?e"
66	6C 73616C617279	txt "salary"
6D	4C 3F73616C	txt "?sal"
72	0E 15 00 0700 030000 00 63000000 660000 00 6D000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @63 → @66 → @6D
87	0E 11 00 0300 020000 00 4E000000 720000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @4E → @72
98	5C 3F64657074	txt "?dept"
9E	5C 3F64657074	txt "?dept"
A4	0E 11 00 0300 020000 00 98000000 9E0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @98 → @9E
B5	5C 7768657265	txt "where"
BB	0F 0A B5000000 8700 0000	map leaf, size 10: @B5 → @87
C5	4C 66696E64	txt "find"
CA	0F 0A C5000000 2F00 0000	map leaf, size 10: @C5 → @2F
D4	7C 67726F75704279	txt "groupBy"
DC	0F 0A D4000000 A400 0000	map leaf, size 10: @D4 → @A4
E6	07 12 0A040000 BB00 0000 CA000000 DC00 0000	map branch, size 18, depth 0 (root), slots 1, 3, 10: @BB → @CA → @DC
F8	E6000000 00000000	root → @E6; previous: none

Window functions#

A window entry has three parts: the function with its arguments, the variable that holds the result, and an optional configuration. The configuration holds partitionBy, orderBy and frame.

rowNumber, rank, denseRank, percentRank and cumeDist have no argument. They read the window's own orderBy.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 3F6E616D65	txt "?name"
00A	6C 3F706C616365	txt "?place"
011	0E 11 00 0300 020000 00 04000000 0A0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @4 → @A
022	2C 3F65	txt "?e"
025	6C 73616C617279	txt "salary"
02C	4C 3F73616C	txt "?sal"
031	0E 15 00 0700 030000 00 22000000 250000 00 2C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @22 → @25 → @2C
046	2C 3F65	txt "?e"
049	4C 6E616D65	txt "name"
04E	5C 3F6E616D65	txt "?name"
054	0E 15 00 0700 030000 00 46000000 490000 00 4E000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @46 → @49 → @4E
069	0E 11 00 0300 020000 00 31000000 540000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @31 → @54
07A	9C 726F774E756D62 6572	txt "rowNumber"
084	0E 0D 00 0100 010000 00 7A000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @7A
091	4C 3F73616C	txt "?sal"
096	4C 64657363	txt "desc"
09B	0E 11 00 0300 020000 00 91000000 960000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @91 → @96
0AC	0E 0D 00 0100 010000 00 9B000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @9B
0B9	7C 6F726465724279	txt "orderBy"
0C1	0F 0A B9000000 AC00 0000	map leaf, size 10: @B9 → @AC
0CB	6C 3F706C616365	txt "?place"
0D2	0E 15 00 0700 030000 00 84000000 CB0000 00 C1000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @84 → @CB → @C1
0E7	0E 0D 00 0100 010000 00 D2000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @D2
0F4	4C 3F73616C	txt "?sal"
0F9	4C 64657363	txt "desc"
0FE	0E 11 00 0300 020000 00 F4000000 F90000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @F4 → @F9
10F	0E 0D 00 0100 010000 00 FE000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @FE
11C	5C 7768657265	txt "where"
122	0F 0A 1C010000 6900 0000	map leaf, size 10: @11C → @69
12C	4C 66696E64	txt "find"
131	0F 0A 2C010000 1100 0000	map leaf, size 10: @12C → @11
13B	7C 6F726465724279	txt "orderBy"
143	0F 0A 3B010000 0F01 0000	map leaf, size 10: @13B → @10F
14D	6C 77696E646F77	txt "window"
154	0F 0A 4D010000 E700 0000	map leaf, size 10: @14D → @E7
15E	07 16 2A400000 2201 0000 31010000 4301 0000 54010000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 14: @122 → @131 → @143 → @154
174	5E010000 00000000	root → @15E; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 706C616365	txt "place"
00A	02 01000000000000 00	i64 1
013	0F 0A 04000000 0A00 0000	map leaf, size 10: @4 → @A
01D	4C 6E616D65	txt "name"
022	3C 416461	txt "Ada"
026	0F 0A 1D000000 2200 0000	map leaf, size 10: @1D → @22
030	07 0E 00A00000 1300 0000 26000000	map branch, size 14, depth 0 (root), slots 13, 15: @13 → @26
03E	5C 706C616365	txt "place"
044	02 02000000000000 00	i64 2
04D	0F 0A 3E000000 4400 0000	map leaf, size 10: @3E → @44
057	4C 6E616D65	txt "name"
05C	3C 426F62	txt "Bob"
060	0F 0A 57000000 5C00 0000	map leaf, size 10: @57 → @5C
06A	07 0E 00A00000 4D00 0000 60000000	map branch, size 14, depth 0 (root), slots 13, 15: @4D → @60
078	5C 706C616365	txt "place"
07E	02 03000000000000 00	i64 3
087	0F 0A 78000000 7E00 0000	map leaf, size 10: @78 → @7E
091	4C 6E616D65	txt "name"
096	2C 4379	txt "Cy"
099	0F 0A 91000000 9600 0000	map leaf, size 10: @91 → @96
0A3	07 0E 00A00000 8700 0000 99000000	map branch, size 14, depth 0 (root), slots 13, 15: @87 → @99
0B1	5C 706C616365	txt "place"
0B7	02 04000000000000 00	i64 4
0C0	0F 0A B1000000 B700 0000	map leaf, size 10: @B1 → @B7
0CA	4C 6E616D65	txt "name"
0CF	3C 457665	txt "Eve"
0D3	0F 0A CA000000 CF00 0000	map leaf, size 10: @CA → @CF
0DD	07 0E 00A00000 C000 0000 D3000000	map branch, size 14, depth 0 (root), slots 13, 15: @C0 → @D3
0EB	5C 706C616365	txt "place"
0F1	02 05000000000000 00	i64 5
0FA	0F 0A EB000000 F100 0000	map leaf, size 10: @EB → @F1
104	4C 6E616D65	txt "name"
109	3C 44616E	txt "Dan"
10D	0F 0A 04010000 0901 0000	map leaf, size 10: @104 → @109
117	07 0E 00A00000 FA00 0000 0D010000	map branch, size 14, depth 0 (root), slots 13, 15: @FA → @10D
125	0E 1D 00 1F00 050000 00 30000000 6A0000 00 A3000000 DD0000 00 17010000	array leaf, size 29, shift 0, bitmap 001F, count 5 (root): @30 → @6A → @A3 → @DD → @117
142	25010000 00000000	root → @125; previous: none

An argument for one of these functions is an error.

Offset (hex)	Bytes (hex)	Meaning
00	54 52 4F 4E	magic TRON
04	4C 72616E6B	txt "rank"
09	4C 3F73616C	txt "?sal"
0E	0E 11 00 0300 020000 00 04000000 090000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @4 → @9
1F	4C 3F73616C	txt "?sal"
24	4C 64657363	txt "desc"
29	0E 11 00 0300 020000 00 1F000000 240000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @1F → @24
3A	0E 0D 00 0100 010000 00 29000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @29
47	7C 6F726465724279	txt "orderBy"
4F	0F 0A 47000000 3A00 0000	map leaf, size 10: @47 → @3A
59	6C 3F706C616365	txt "?place"
60	0E 15 00 0700 030000 00 0E000000 590000 00 4F000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @E → @59 → @4F
75	0E 0D 00 0100 010000 00 60000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @60
82	6C 77696E646F77	txt "window"
89	0F 0A 82000000 7500 0000	map leaf, size 10: @82 → @75
93	89000000 00000000	root → @89; previous: none

Ranks and ties#

The position functions differ when rows share a value. rank gives rows with the same value the same number and skips the numbers after them. denseRank does not skip. percentRank gives (rank - 1) / (rows - 1). cumeDist gives the part of the rows that have the same value or come before it. This query orders by dept, so the two eng rows and the two sales rows are equal.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 3F6E616D65	txt "?name"
00A	5C 3F64657074	txt "?dept"
010	5C 3F72616E6B	txt "?rank"
016	6C 3F64656E7365	txt "?dense"
01D	4C 3F706374	txt "?pct"
022	5C 3F63756D65	txt "?cume"
028	0E 21 00 3F00 060000 00 04000000 0A0000 00 10000000 160000 00 1D000000 220000 00	array leaf, size 33, shift 0, bitmap 003F, count 6 (root): @4 → @A → @10 → @16 → @1D → @22
049	2C 3F65	txt "?e"
04C	4C 64657074	txt "dept"
051	5C 3F64657074	txt "?dept"
057	0E 15 00 0700 030000 00 49000000 4C0000 00 51000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @49 → @4C → @51
06C	2C 3F65	txt "?e"
06F	4C 6E616D65	txt "name"
074	5C 3F6E616D65	txt "?name"
07A	0E 15 00 0700 030000 00 6C000000 6F0000 00 74000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @6C → @6F → @74
08F	0E 11 00 0300 020000 00 57000000 7A0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @57 → @7A
0A0	4C 72616E6B	txt "rank"
0A5	0E 0D 00 0100 010000 00 A0000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @A0
0B2	5C 3F64657074	txt "?dept"
0B8	0E 0D 00 0100 010000 00 B2000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @B2
0C5	7C 6F726465724279	txt "orderBy"
0CD	0F 0A C5000000 B800 0000	map leaf, size 10: @C5 → @B8
0D7	5C 3F72616E6B	txt "?rank"
0DD	0E 15 00 0700 030000 00 A5000000 D70000 00 CD000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @A5 → @D7 → @CD
0F2	9C 64656E73655261 6E6B	txt "denseRank"
0FC	0E 0D 00 0100 010000 00 F2000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @F2
109	5C 3F64657074	txt "?dept"
10F	0E 0D 00 0100 010000 00 09010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @109
11C	7C 6F726465724279	txt "orderBy"
124	0F 0A 1C010000 0F01 0000	map leaf, size 10: @11C → @10F
12E	6C 3F64656E7365	txt "?dense"

Show all 61 nodes

Offset (hex)	Bytes (hex)	Meaning
135	0E 15 00 0700 030000 00 FC000000 2E0100 00 24010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @FC → @12E → @124
14A	BC 70657263656E74 52616E6B	txt "percentRank"
156	0E 0D 00 0100 010000 00 4A010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @14A
163	5C 3F64657074	txt "?dept"
169	0E 0D 00 0100 010000 00 63010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @163
176	7C 6F726465724279	txt "orderBy"
17E	0F 0A 76010000 6901 0000	map leaf, size 10: @176 → @169
188	4C 3F706374	txt "?pct"
18D	0E 15 00 0700 030000 00 56010000 880100 00 7E010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @156 → @188 → @17E
1A2	8C 63756D65446973 74	txt "cumeDist"
1AB	0E 0D 00 0100 010000 00 A2010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @1A2
1B8	5C 3F64657074	txt "?dept"
1BE	0E 0D 00 0100 010000 00 B8010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @1B8
1CB	7C 6F726465724279	txt "orderBy"
1D3	0F 0A CB010000 BE01 0000	map leaf, size 10: @1CB → @1BE
1DD	5C 3F63756D65	txt "?cume"
1E3	0E 15 00 0700 030000 00 AB010000 DD0100 00 D3010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @1AB → @1DD → @1D3
1F8	0E 19 00 0F00 040000 00 DD000000 350100 00 8D010000 E30100 00	array leaf, size 25, shift 0, bitmap 000F, count 4 (root): @DD → @135 → @18D → @1E3
211	5C 3F64657074	txt "?dept"
217	5C 3F6E616D65	txt "?name"
21D	0E 11 00 0300 020000 00 11020000 170200 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @211 → @217
22E	5C 7768657265	txt "where"
234	0F 0A 2E020000 8F00 0000	map leaf, size 10: @22E → @8F
23E	4C 66696E64	txt "find"
243	0F 0A 3E020000 2800 0000	map leaf, size 10: @23E → @28
24D	7C 6F726465724279	txt "orderBy"
255	0F 0A 4D020000 1D02 0000	map leaf, size 10: @24D → @21D
25F	6C 77696E646F77	txt "window"
266	0F 0A 5F020000 F801 0000	map leaf, size 10: @25F → @1F8
270	07 16 2A400000 3402 0000 43020000 5502 0000 66020000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 14: @234 → @243 → @255 → @266

Offset (hex)	Bytes (hex)	Meaning
286	70020000 00000000	root → @270; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	4C 63756D65	txt "cume"
009	03 9A9999999999D9 3F	f64 0.4
012	0F 0A 04000000 0900 0000	map leaf, size 10: @4 → @9
01C	5C 64656E7365	txt "dense"
022	02 01000000000000 00	i64 1
02B	0F 0A 1C000000 2200 0000	map leaf, size 10: @1C → @22
035	4C 72616E6B	txt "rank"
03A	02 01000000000000 00	i64 1
043	0F 0A 35000000 3A00 0000	map leaf, size 10: @35 → @3A
04D	3C 706374	txt "pct"
051	02 00000000000000 00	i64 0
05A	0F 0A 4D000000 5100 0000	map leaf, size 10: @4D → @51
064	4C 64657074	txt "dept"
069	3C 656E67	txt "eng"
06D	0F 0A 64000000 6900 0000	map leaf, size 10: @64 → @69
077	4C 6E616D65	txt "name"
07C	3C 416461	txt "Ada"
080	0F 0A 77000000 7C00 0000	map leaf, size 10: @77 → @7C
08A	07 1E 449C0000 1200 0000 2B000000 4300 0000 5A000000 6D00 0000 80000000	map branch, size 30, depth 0 (root), slots 2, 6, 10, 11, 12, 15: @12 → @2B → @43 → @5A → @6D → @80
0A8	4C 63756D65	txt "cume"
0AD	03 9A9999999999D9 3F	f64 0.4
0B6	0F 0A A8000000 AD00 0000	map leaf, size 10: @A8 → @AD
0C0	5C 64656E7365	txt "dense"
0C6	02 01000000000000 00	i64 1
0CF	0F 0A C0000000 C600 0000	map leaf, size 10: @C0 → @C6
0D9	4C 72616E6B	txt "rank"
0DE	02 01000000000000 00	i64 1
0E7	0F 0A D9000000 DE00 0000	map leaf, size 10: @D9 → @DE
0F1	3C 706374	txt "pct"
0F5	02 00000000000000 00	i64 0
0FE	0F 0A F1000000 F500 0000	map leaf, size 10: @F1 → @F5

Show all 96 nodes

Offset (hex)	Bytes (hex)	Meaning
108	4C 64657074	txt "dept"
10D	3C 656E67	txt "eng"
111	0F 0A 08010000 0D01 0000	map leaf, size 10: @108 → @10D
11B	4C 6E616D65	txt "name"
120	3C 426F62	txt "Bob"
124	0F 0A 1B010000 2001 0000	map leaf, size 10: @11B → @120
12E	07 1E 449C0000 B600 0000 CF000000 E700 0000 FE000000 1101 0000 24010000	map branch, size 30, depth 0 (root), slots 2, 6, 10, 11, 12, 15: @B6 → @CF → @E7 → @FE → @111 → @124
14C	4C 63756D65	txt "cume"
151	03 333333333333E3 3F	f64 0.6
15A	0F 0A 4C010000 5101 0000	map leaf, size 10: @14C → @151
164	5C 64656E7365	txt "dense"
16A	02 02000000000000 00	i64 2
173	0F 0A 64010000 6A01 0000	map leaf, size 10: @164 → @16A
17D	4C 72616E6B	txt "rank"
182	02 03000000000000 00	i64 3
18B	0F 0A 7D010000 8201 0000	map leaf, size 10: @17D → @182
195	3C 706374	txt "pct"
199	03 000000000000E0 3F	f64 0.5
1A2	0F 0A 95010000 9901 0000	map leaf, size 10: @195 → @199
1AC	4C 64657074	txt "dept"
1B1	3C 6F7073	txt "ops"
1B5	0F 0A AC010000 B101 0000	map leaf, size 10: @1AC → @1B1
1BF	4C 6E616D65	txt "name"
1C4	3C 457665	txt "Eve"
1C8	0F 0A BF010000 C401 0000	map leaf, size 10: @1BF → @1C4
1D2	07 1E 449C0000 5A01 0000 73010000 8B01 0000 A2010000 B501 0000 C8010000	map branch, size 30, depth 0 (root), slots 2, 6, 10, 11, 12, 15: @15A → @173 → @18B → @1A2 → @1B5 → @1C8
1F0	4C 63756D65	txt "cume"
1F5	02 01000000000000 00	i64 1
1FE	0F 0A F0010000 F501 0000	map leaf, size 10: @1F0 → @1F5
208	5C 64656E7365	txt "dense"
20E	02 03000000000000 00	i64 3
217	0F 0A 08020000 0E02 0000	map leaf, size 10: @208 → @20E
221	4C 72616E6B	txt "rank"
226	02 04000000000000 00	i64 4
22F	0F 0A 21020000 2602 0000	map leaf, size 10: @221 → @226
239	3C 706374	txt "pct"
23D	03 000000000000E8 3F	f64 0.75
246	0F 0A 39020000 3D02 0000	map leaf, size 10: @239 → @23D
250	4C 64657074	txt "dept"
255	5C 73616C6573	txt "sales"
25B	0F 0A 50020000 5502 0000	map leaf, size 10: @250 → @255
265	4C 6E616D65	txt "name"
26A	2C 4379	txt "Cy"
26D	0F 0A 65020000 6A02 0000	map leaf, size 10: @265 → @26A
277	07 1E 449C0000 FE01 0000 17020000 2F02 0000 46020000 5B02 0000 6D020000	map branch, size 30, depth 0 (root), slots 2, 6, 10, 11, 12, 15: @1FE → @217 → @22F → @246 → @25B → @26D
295	4C 63756D65	txt "cume"
29A	02 01000000000000 00	i64 1
2A3	0F 0A 95020000 9A02 0000	map leaf, size 10: @295 → @29A
2AD	5C 64656E7365	txt "dense"
2B3	02 03000000000000 00	i64 3
2BC	0F 0A AD020000 B302 0000	map leaf, size 10: @2AD → @2B3
2C6	4C 72616E6B	txt "rank"
2CB	02 04000000000000 00	i64 4
2D4	0F 0A C6020000 CB02 0000	map leaf, size 10: @2C6 → @2CB
2DE	3C 706374	txt "pct"
2E2	03 000000000000E8 3F	f64 0.75
2EB	0F 0A DE020000 E202 0000	map leaf, size 10: @2DE → @2E2
2F5	4C 64657074	txt "dept"
2FA	5C 73616C6573	txt "sales"
300	0F 0A F5020000 FA02 0000	map leaf, size 10: @2F5 → @2FA
30A	4C 6E616D65	txt "name"
30F	3C 44616E	txt "Dan"
313	0F 0A 0A030000 0F03 0000	map leaf, size 10: @30A → @30F
31D	07 1E 449C0000 A302 0000 BC020000 D402 0000 EB020000 0003 0000 13030000	map branch, size 30, depth 0 (root), slots 2, 6, 10, 11, 12, 15: @2A3 → @2BC → @2D4 → @2EB → @300 → @313
33B	0E 1D 00 1F00 050000 00 8A000000 2E0100 00 D2010000 770200 00 1D030000	array leaf, size 29, shift 0, bitmap 001F, count 5 (root): @8A → @12E → @1D2 → @277 → @31D

Offset (hex)	Bytes (hex)	Meaning
358	3B030000 00000000	root → @33B; previous: none

Previous and next rows#

partitionBy restarts the window for each group. lag reads the row before the current row, and gives null at the start of each partition.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 3F64657074	txt "?dept"
00A	5C 3F6E616D65	txt "?name"
010	5C 3F70726576	txt "?prev"
016	0E 15 00 0700 030000 00 04000000 0A0000 00 10000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4 → @A → @10
02B	2C 3F65	txt "?e"
02E	4C 64657074	txt "dept"
033	5C 3F64657074	txt "?dept"
039	0E 15 00 0700 030000 00 2B000000 2E0000 00 33000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @2B → @2E → @33
04E	2C 3F65	txt "?e"
051	6C 73616C617279	txt "salary"
058	4C 3F73616C	txt "?sal"
05D	0E 15 00 0700 030000 00 4E000000 510000 00 58000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4E → @51 → @58
072	2C 3F65	txt "?e"
075	4C 6E616D65	txt "name"
07A	5C 3F6E616D65	txt "?name"
080	0E 15 00 0700 030000 00 72000000 750000 00 7A000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @72 → @75 → @7A
095	0E 15 00 0700 030000 00 39000000 5D0000 00 80000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @39 → @5D → @80
0AA	3C 6C6167	txt "lag"
0AE	4C 3F73616C	txt "?sal"
0B3	0E 11 00 0300 020000 00 AA000000 AE0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @AA → @AE
0C4	5C 3F64657074	txt "?dept"
0CA	0E 0D 00 0100 010000 00 C4000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @C4
0D7	4C 3F73616C	txt "?sal"
0DC	4C 64657363	txt "desc"
0E1	0E 11 00 0300 020000 00 D7000000 DC0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @D7 → @DC
0F2	0E 0D 00 0100 010000 00 E1000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @E1
0FF	BC 70617274697469 6F6E4279	txt "partitionBy"
10B	0F 0A FF000000 CA00 0000	map leaf, size 10: @FF → @CA
115	7C 6F726465724279	txt "orderBy"
11D	0F 0A 15010000 F200 0000	map leaf, size 10: @115 → @F2
127	07 0E 21000000 0B01 0000 1D010000	map branch, size 14, depth 0 (root), slots 0, 5: @10B → @11D
135	5C 3F70726576	txt "?prev"
13B	0E 15 00 0700 030000 00 B3000000 350100 00 27010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @B3 → @135 → @127
150	0E 0D 00 0100 010000 00 3B010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @13B
15D	4C 3F73616C	txt "?sal"
162	4C 64657363	txt "desc"
167	0E 11 00 0300 020000 00 5D010000 620100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @15D → @162
178	5C 3F64657074	txt "?dept"
17E	0E 11 00 0300 020000 00 78010000 670100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @178 → @167
18F	5C 7768657265	txt "where"
195	0F 0A 8F010000 9500 0000	map leaf, size 10: @18F → @95
19F	4C 66696E64	txt "find"
1A4	0F 0A 9F010000 1600 0000	map leaf, size 10: @19F → @16
1AE	7C 6F726465724279	txt "orderBy"
1B6	0F 0A AE010000 7E01 0000	map leaf, size 10: @1AE → @17E
1C0	6C 77696E646F77	txt "window"
1C7	0F 0A C0010000 5001 0000	map leaf, size 10: @1C0 → @150
1D1	07 16 2A400000 9501 0000 A4010000 B601 0000 C7010000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 14: @195 → @1A4 → @1B6 → @1C7
1E7	D1010000 00000000	root → @1D1; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	4C 64657074	txt "dept"
009	3C 656E67	txt "eng"
00D	0F 0A 04000000 0900 0000	map leaf, size 10: @4 → @9
017	4C 70726576	txt "prev"
01C	00	nil
01D	0F 0A 17000000 1C00 0000	map leaf, size 10: @17 → @1C
027	4C 6E616D65	txt "name"
02C	3C 416461	txt "Ada"
030	0F 0A 27000000 2C00 0000	map leaf, size 10: @27 → @2C
03A	07 12 00B00000 0D00 0000 1D000000 3000 0000	map branch, size 18, depth 0 (root), slots 12, 13, 15: @D → @1D → @30
04C	4C 64657074	txt "dept"
051	3C 656E67	txt "eng"
055	0F 0A 4C000000 5100 0000	map leaf, size 10: @4C → @51
05F	4C 70726576	txt "prev"
064	02 78000000000000 00	i64 120
06D	0F 0A 5F000000 6400 0000	map leaf, size 10: @5F → @64
077	4C 6E616D65	txt "name"
07C	3C 426F62	txt "Bob"
080	0F 0A 77000000 7C00 0000	map leaf, size 10: @77 → @7C
08A	07 12 00B00000 5500 0000 6D000000 8000 0000	map branch, size 18, depth 0 (root), slots 12, 13, 15: @55 → @6D → @80
09C	4C 64657074	txt "dept"
0A1	3C 6F7073	txt "ops"
0A5	0F 0A 9C000000 A100 0000	map leaf, size 10: @9C → @A1
0AF	4C 70726576	txt "prev"
0B4	00	nil
0B5	0F 0A AF000000 B400 0000	map leaf, size 10: @AF → @B4
0BF	4C 6E616D65	txt "name"
0C4	3C 457665	txt "Eve"
0C8	0F 0A BF000000 C400 0000	map leaf, size 10: @BF → @C4
0D2	07 12 00B00000 A500 0000 B5000000 C800 0000	map branch, size 18, depth 0 (root), slots 12, 13, 15: @A5 → @B5 → @C8
0E4	4C 64657074	txt "dept"
0E9	5C 73616C6573	txt "sales"
0EF	0F 0A E4000000 E900 0000	map leaf, size 10: @E4 → @E9
0F9	4C 70726576	txt "prev"
0FE	00	nil
0FF	0F 0A F9000000 FE00 0000	map leaf, size 10: @F9 → @FE
109	4C 6E616D65	txt "name"
10E	2C 4379	txt "Cy"
111	0F 0A 09010000 0E01 0000	map leaf, size 10: @109 → @10E
11B	07 12 00B00000 EF00 0000 FF000000 1101 0000	map branch, size 18, depth 0 (root), slots 12, 13, 15: @EF → @FF → @111
12D	4C 64657074	txt "dept"
132	5C 73616C6573	txt "sales"
138	0F 0A 2D010000 3201 0000	map leaf, size 10: @12D → @132
142	4C 70726576	txt "prev"
147	02 5A000000000000 00	i64 90
150	0F 0A 42010000 4701 0000	map leaf, size 10: @142 → @147
15A	4C 6E616D65	txt "name"
15F	3C 44616E	txt "Dan"
163	0F 0A 5A010000 5F01 0000	map leaf, size 10: @15A → @15F
16D	07 12 00B00000 3801 0000 50010000 6301 0000	map branch, size 18, depth 0 (root), slots 12, 13, 15: @138 → @150 → @163
17F	0E 1D 00 1F00 050000 00 3A000000 8A0000 00 D2000000 1B0100 00 6D010000	array leaf, size 29, shift 0, bitmap 001F, count 5 (root): @3A → @8A → @D2 → @11B → @16D
19C	7F010000 00000000	root → @17F; previous: none

lead reads the row after the current row. The second argument sets how many rows to move, and the third argument replaces null at the end of each partition.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 3F64657074	txt "?dept"
00A	5C 3F6E616D65	txt "?name"
010	5C 3F6E657874	txt "?next"
016	0E 15 00 0700 030000 00 04000000 0A0000 00 10000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4 → @A → @10
02B	2C 3F65	txt "?e"
02E	4C 64657074	txt "dept"
033	5C 3F64657074	txt "?dept"
039	0E 15 00 0700 030000 00 2B000000 2E0000 00 33000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @2B → @2E → @33
04E	2C 3F65	txt "?e"
051	6C 73616C617279	txt "salary"
058	4C 3F73616C	txt "?sal"
05D	0E 15 00 0700 030000 00 4E000000 510000 00 58000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @4E → @51 → @58
072	2C 3F65	txt "?e"
075	4C 6E616D65	txt "name"
07A	5C 3F6E616D65	txt "?name"
080	0E 15 00 0700 030000 00 72000000 750000 00 7A000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @72 → @75 → @7A
095	0E 15 00 0700 030000 00 39000000 5D0000 00 80000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @39 → @5D → @80
0AA	4C 6C656164	txt "lead"
0AF	4C 3F73616C	txt "?sal"
0B4	02 01000000000000 00	i64 1
0BD	02 00000000000000 00	i64 0
0C6	0E 19 00 0F00 040000 00 AA000000 AF0000 00 B4000000 BD0000 00	array leaf, size 25, shift 0, bitmap 000F, count 4 (root): @AA → @AF → @B4 → @BD
0DF	5C 3F64657074	txt "?dept"
0E5	0E 0D 00 0100 010000 00 DF000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @DF
0F2	4C 3F73616C	txt "?sal"
0F7	4C 64657363	txt "desc"
0FC	0E 11 00 0300 020000 00 F2000000 F70000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @F2 → @F7
10D	0E 0D 00 0100 010000 00 FC000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @FC
11A	BC 70617274697469 6F6E4279	txt "partitionBy"
126	0F 0A 1A010000 E500 0000	map leaf, size 10: @11A → @E5
130	7C 6F726465724279	txt "orderBy"

Show all 50 nodes

Offset (hex)	Bytes (hex)	Meaning
138	0F 0A 30010000 0D01 0000	map leaf, size 10: @130 → @10D
142	07 0E 21000000 2601 0000 38010000	map branch, size 14, depth 0 (root), slots 0, 5: @126 → @138
150	5C 3F6E657874	txt "?next"
156	0E 15 00 0700 030000 00 C6000000 500100 00 42010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @C6 → @150 → @142
16B	0E 0D 00 0100 010000 00 56010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @156
178	4C 3F73616C	txt "?sal"
17D	4C 64657363	txt "desc"
182	0E 11 00 0300 020000 00 78010000 7D0100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @178 → @17D
193	5C 3F64657074	txt "?dept"
199	0E 11 00 0300 020000 00 93010000 820100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @193 → @182
1AA	5C 7768657265	txt "where"
1B0	0F 0A AA010000 9500 0000	map leaf, size 10: @1AA → @95
1BA	4C 66696E64	txt "find"
1BF	0F 0A BA010000 1600 0000	map leaf, size 10: @1BA → @16
1C9	7C 6F726465724279	txt "orderBy"
1D1	0F 0A C9010000 9901 0000	map leaf, size 10: @1C9 → @199
1DB	6C 77696E646F77	txt "window"
1E2	0F 0A DB010000 6B01 0000	map leaf, size 10: @1DB → @16B
1EC	07 16 2A400000 B001 0000 BF010000 D101 0000 E2010000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 14: @1B0 → @1BF → @1D1 → @1E2

Offset (hex)	Bytes (hex)	Meaning
202	EC010000 00000000	root → @1EC; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	4C 6E657874	txt "next"
009	02 64000000000000 00	i64 100
012	0F 0A 04000000 0900 0000	map leaf, size 10: @4 → @9
01C	4C 64657074	txt "dept"
021	3C 656E67	txt "eng"
025	0F 0A 1C000000 2100 0000	map leaf, size 10: @1C → @21
02F	4C 6E616D65	txt "name"
034	3C 416461	txt "Ada"
038	0F 0A 2F000000 3400 0000	map leaf, size 10: @2F → @34
042	07 12 00980000 1200 0000 25000000 3800 0000	map branch, size 18, depth 0 (root), slots 11, 12, 15: @12 → @25 → @38
054	4C 6E657874	txt "next"
059	02 00000000000000 00	i64 0
062	0F 0A 54000000 5900 0000	map leaf, size 10: @54 → @59
06C	4C 64657074	txt "dept"
071	3C 656E67	txt "eng"
075	0F 0A 6C000000 7100 0000	map leaf, size 10: @6C → @71
07F	4C 6E616D65	txt "name"
084	3C 426F62	txt "Bob"
088	0F 0A 7F000000 8400 0000	map leaf, size 10: @7F → @84
092	07 12 00980000 6200 0000 75000000 8800 0000	map branch, size 18, depth 0 (root), slots 11, 12, 15: @62 → @75 → @88
0A4	4C 6E657874	txt "next"
0A9	02 00000000000000 00	i64 0
0B2	0F 0A A4000000 A900 0000	map leaf, size 10: @A4 → @A9
0BC	4C 64657074	txt "dept"
0C1	3C 6F7073	txt "ops"
0C5	0F 0A BC000000 C100 0000	map leaf, size 10: @BC → @C1
0CF	4C 6E616D65	txt "name"
0D4	3C 457665	txt "Eve"
0D8	0F 0A CF000000 D400 0000	map leaf, size 10: @CF → @D4
0E2	07 12 00980000 B200 0000 C5000000 D800 0000	map branch, size 18, depth 0 (root), slots 11, 12, 15: @B2 → @C5 → @D8
0F4	4C 6E657874	txt "next"
0F9	02 46000000000000 00	i64 70
102	0F 0A F4000000 F900 0000	map leaf, size 10: @F4 → @F9
10C	4C 64657074	txt "dept"
111	5C 73616C6573	txt "sales"
117	0F 0A 0C010000 1101 0000	map leaf, size 10: @10C → @111
121	4C 6E616D65	txt "name"
126	2C 4379	txt "Cy"
129	0F 0A 21010000 2601 0000	map leaf, size 10: @121 → @126
133	07 12 00980000 0201 0000 17010000 2901 0000	map branch, size 18, depth 0 (root), slots 11, 12, 15: @102 → @117 → @129
145	4C 6E657874	txt "next"
14A	02 00000000000000 00	i64 0
153	0F 0A 45010000 4A01 0000	map leaf, size 10: @145 → @14A
15D	4C 64657074	txt "dept"
162	5C 73616C6573	txt "sales"
168	0F 0A 5D010000 6201 0000	map leaf, size 10: @15D → @162
172	4C 6E616D65	txt "name"
177	3C 44616E	txt "Dan"
17B	0F 0A 72010000 7701 0000	map leaf, size 10: @172 → @177
185	07 12 00980000 5301 0000 68010000 7B01 0000	map branch, size 18, depth 0 (root), slots 11, 12, 15: @153 → @168 → @17B
197	0E 1D 00 1F00 050000 00 42000000 920000 00 E2000000 330100 00 85010000	array leaf, size 29, shift 0, bitmap 001F, count 5 (root): @42 → @92 → @E2 → @133 → @185
1B4	97010000 00000000	root → @197; previous: none

Values for each group#

A number function without orderBy reads the full partition. Each row gets the value for its group, and the rows stay separate.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 3F64657074	txt "?dept"
00A	5C 3F6E616D65	txt "?name"
010	5C 3F6D65616E	txt "?mean"
016	5C 3F73697A65	txt "?size"
01C	0E 19 00 0F00 040000 00 04000000 0A0000 00 10000000 160000 00	array leaf, size 25, shift 0, bitmap 000F, count 4 (root): @4 → @A → @10 → @16
035	2C 3F65	txt "?e"
038	4C 64657074	txt "dept"
03D	5C 3F64657074	txt "?dept"
043	0E 15 00 0700 030000 00 35000000 380000 00 3D000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @35 → @38 → @3D
058	2C 3F65	txt "?e"
05B	6C 73616C617279	txt "salary"
062	4C 3F73616C	txt "?sal"
067	0E 15 00 0700 030000 00 58000000 5B0000 00 62000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @58 → @5B → @62
07C	2C 3F65	txt "?e"
07F	4C 6E616D65	txt "name"
084	5C 3F6E616D65	txt "?name"
08A	0E 15 00 0700 030000 00 7C000000 7F0000 00 84000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @7C → @7F → @84
09F	0E 15 00 0700 030000 00 43000000 670000 00 8A000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @43 → @67 → @8A
0B4	4C 6D65616E	txt "mean"
0B9	4C 3F73616C	txt "?sal"
0BE	0E 11 00 0300 020000 00 B4000000 B90000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @B4 → @B9
0CF	5C 3F64657074	txt "?dept"
0D5	0E 0D 00 0100 010000 00 CF000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @CF
0E2	BC 70617274697469 6F6E4279	txt "partitionBy"
0EE	0F 0A E2000000 D500 0000	map leaf, size 10: @E2 → @D5
0F8	5C 3F6D65616E	txt "?mean"
0FE	0E 15 00 0700 030000 00 BE000000 F80000 00 EE000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @BE → @F8 → @EE
113	5C 636F756E74	txt "count"
119	2C 3F65	txt "?e"
11C	0E 11 00 0300 020000 00 13010000 190100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @113 → @119
12D	5C 3F64657074	txt "?dept"
133	0E 0D 00 0100 010000 00 2D010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @12D
140	BC 70617274697469 6F6E4279	txt "partitionBy"
14C	0F 0A 40010000 3301 0000	map leaf, size 10: @140 → @133
156	5C 3F73697A65	txt "?size"
15C	0E 15 00 0700 030000 00 1C010000 560100 00 4C010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @11C → @156 → @14C
171	0E 11 00 0300 020000 00 FE000000 5C0100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @FE → @15C
182	5C 3F64657074	txt "?dept"
188	5C 3F6E616D65	txt "?name"
18E	0E 11 00 0300 020000 00 82010000 880100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @182 → @188
19F	5C 7768657265	txt "where"
1A5	0F 0A 9F010000 9F00 0000	map leaf, size 10: @19F → @9F
1AF	4C 66696E64	txt "find"
1B4	0F 0A AF010000 1C00 0000	map leaf, size 10: @1AF → @1C
1BE	7C 6F726465724279	txt "orderBy"
1C6	0F 0A BE010000 8E01 0000	map leaf, size 10: @1BE → @18E
1D0	6C 77696E646F77	txt "window"
1D7	0F 0A D0010000 7101 0000	map leaf, size 10: @1D0 → @171
1E1	07 16 2A400000 A501 0000 B4010000 C601 0000 D7010000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 14: @1A5 → @1B4 → @1C6 → @1D7
1F7	E1010000 00000000	root → @1E1; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	4C 6D65616E	txt "mean"
009	02 6E000000000000 00	i64 110
012	0F 0A 04000000 0900 0000	map leaf, size 10: @4 → @9
01C	4C 73697A65	txt "size"
021	02 02000000000000 00	i64 2
02A	0F 0A 1C000000 2100 0000	map leaf, size 10: @1C → @21
034	07 0E 05000000 1200 0000 2A000000	map branch, size 14, depth 1, slots 0, 2: @12 → @2A
042	4C 64657074	txt "dept"
047	3C 656E67	txt "eng"
04B	0F 0A 42000000 4700 0000	map leaf, size 10: @42 → @47
055	4C 6E616D65	txt "name"
05A	3C 416461	txt "Ada"
05E	0F 0A 55000000 5A00 0000	map leaf, size 10: @55 → @5A
068	07 12 00910000 3400 0000 4B000000 5E00 0000	map branch, size 18, depth 0 (root), slots 8, 12, 15: @34 → @4B → @5E
07A	4C 6D65616E	txt "mean"
07F	02 6E000000000000 00	i64 110
088	0F 0A 7A000000 7F00 0000	map leaf, size 10: @7A → @7F
092	4C 73697A65	txt "size"
097	02 02000000000000 00	i64 2
0A0	0F 0A 92000000 9700 0000	map leaf, size 10: @92 → @97
0AA	07 0E 05000000 8800 0000 A0000000	map branch, size 14, depth 1, slots 0, 2: @88 → @A0
0B8	4C 64657074	txt "dept"
0BD	3C 656E67	txt "eng"
0C1	0F 0A B8000000 BD00 0000	map leaf, size 10: @B8 → @BD
0CB	4C 6E616D65	txt "name"
0D0	3C 426F62	txt "Bob"
0D4	0F 0A CB000000 D000 0000	map leaf, size 10: @CB → @D0
0DE	07 12 00910000 AA00 0000 C1000000 D400 0000	map branch, size 18, depth 0 (root), slots 8, 12, 15: @AA → @C1 → @D4
0F0	4C 6D65616E	txt "mean"
0F5	02 50000000000000 00	i64 80
0FE	0F 0A F0000000 F500 0000	map leaf, size 10: @F0 → @F5

Show all 71 nodes

Offset (hex)	Bytes (hex)	Meaning
108	4C 73697A65	txt "size"
10D	02 01000000000000 00	i64 1
116	0F 0A 08010000 0D01 0000	map leaf, size 10: @108 → @10D
120	07 0E 05000000 FE00 0000 16010000	map branch, size 14, depth 1, slots 0, 2: @FE → @116
12E	4C 64657074	txt "dept"
133	3C 6F7073	txt "ops"
137	0F 0A 2E010000 3301 0000	map leaf, size 10: @12E → @133
141	4C 6E616D65	txt "name"
146	3C 457665	txt "Eve"
14A	0F 0A 41010000 4601 0000	map leaf, size 10: @141 → @146
154	07 12 00910000 2001 0000 37010000 4A01 0000	map branch, size 18, depth 0 (root), slots 8, 12, 15: @120 → @137 → @14A
166	4C 6D65616E	txt "mean"
16B	02 50000000000000 00	i64 80
174	0F 0A 66010000 6B01 0000	map leaf, size 10: @166 → @16B
17E	4C 73697A65	txt "size"
183	02 02000000000000 00	i64 2
18C	0F 0A 7E010000 8301 0000	map leaf, size 10: @17E → @183
196	07 0E 05000000 7401 0000 8C010000	map branch, size 14, depth 1, slots 0, 2: @174 → @18C
1A4	4C 64657074	txt "dept"
1A9	5C 73616C6573	txt "sales"
1AF	0F 0A A4010000 A901 0000	map leaf, size 10: @1A4 → @1A9
1B9	4C 6E616D65	txt "name"
1BE	2C 4379	txt "Cy"
1C1	0F 0A B9010000 BE01 0000	map leaf, size 10: @1B9 → @1BE
1CB	07 12 00910000 9601 0000 AF010000 C101 0000	map branch, size 18, depth 0 (root), slots 8, 12, 15: @196 → @1AF → @1C1
1DD	4C 6D65616E	txt "mean"
1E2	02 50000000000000 00	i64 80
1EB	0F 0A DD010000 E201 0000	map leaf, size 10: @1DD → @1E2
1F5	4C 73697A65	txt "size"
1FA	02 02000000000000 00	i64 2
203	0F 0A F5010000 FA01 0000	map leaf, size 10: @1F5 → @1FA
20D	07 0E 05000000 EB01 0000 03020000	map branch, size 14, depth 1, slots 0, 2: @1EB → @203
21B	4C 64657074	txt "dept"
220	5C 73616C6573	txt "sales"
226	0F 0A 1B020000 2002 0000	map leaf, size 10: @21B → @220
230	4C 6E616D65	txt "name"
235	3C 44616E	txt "Dan"
239	0F 0A 30020000 3502 0000	map leaf, size 10: @230 → @235
243	07 12 00910000 0D02 0000 26020000 3902 0000	map branch, size 18, depth 0 (root), slots 8, 12, 15: @20D → @226 → @239
255	0E 1D 00 1F00 050000 00 68000000 DE0000 00 54010000 CB0100 00 43020000	array leaf, size 29, shift 0, bitmap 001F, count 5 (root): @68 → @DE → @154 → @1CB → @243

Offset (hex)	Bytes (hex)	Meaning
272	55020000 00000000	root → @255; previous: none

Frames#

A frame sets the rows that the function reads. This running total adds each row to the rows above it.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 3F6E616D65	txt "?name"
00A	8C 3F72756E6E696E 67	txt "?running"
013	0E 11 00 0300 020000 00 04000000 0A0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @4 → @A
024	2C 3F65	txt "?e"
027	6C 73616C617279	txt "salary"
02E	4C 3F73616C	txt "?sal"
033	0E 15 00 0700 030000 00 24000000 270000 00 2E000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @24 → @27 → @2E
048	2C 3F65	txt "?e"
04B	4C 6E616D65	txt "name"
050	5C 3F6E616D65	txt "?name"
056	0E 15 00 0700 030000 00 48000000 4B0000 00 50000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @48 → @4B → @50
06B	0E 11 00 0300 020000 00 33000000 560000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @33 → @56
07C	3C 73756D	txt "sum"
080	4C 3F73616C	txt "?sal"
085	0E 11 00 0300 020000 00 7C000000 800000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @7C → @80
096	4C 3F73616C	txt "?sal"
09B	4C 64657363	txt "desc"
0A0	0E 11 00 0300 020000 00 96000000 9B0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @96 → @9B
0B1	0E 0D 00 0100 010000 00 A0000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @A0
0BE	4C 66726F6D	txt "from"
0C3	14 12 756E626F756E 6465645072656365 64696E67	txt "unboundedPreceding"
0D7	0F 0A BE000000 C300 0000	map leaf, size 10: @BE → @C3
0E1	2C 746F	txt "to"
0E4	AC 63757272656E74 526F77	txt "currentRow"
0EF	0F 0A E1000000 E400 0000	map leaf, size 10: @E1 → @E4
0F9	07 0E 00900000 D700 0000 EF000000	map branch, size 14, depth 0 (root), slots 12, 15: @D7 → @EF
107	4C 726F7773	txt "rows"
10C	0F 0A 07010000 F900 0000	map leaf, size 10: @107 → @F9
116	5C 6672616D65	txt "frame"
11C	0F 0A 16010000 0C01 0000	map leaf, size 10: @116 → @10C
126	7C 6F726465724279	txt "orderBy"
12E	0F 0A 26010000 B100 0000	map leaf, size 10: @126 → @B1
138	07 0E 30000000 1C01 0000 2E010000	map branch, size 14, depth 0 (root), slots 4, 5: @11C → @12E
146	8C 3F72756E6E696E 67	txt "?running"
14F	0E 15 00 0700 030000 00 85000000 460100 00 38010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @85 → @146 → @138
164	0E 0D 00 0100 010000 00 4F010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @14F
171	4C 3F73616C	txt "?sal"
176	4C 64657363	txt "desc"
17B	0E 11 00 0300 020000 00 71010000 760100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @171 → @176
18C	0E 0D 00 0100 010000 00 7B010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @17B
199	5C 7768657265	txt "where"
19F	0F 0A 99010000 6B00 0000	map leaf, size 10: @199 → @6B
1A9	4C 66696E64	txt "find"
1AE	0F 0A A9010000 1300 0000	map leaf, size 10: @1A9 → @13
1B8	7C 6F726465724279	txt "orderBy"
1C0	0F 0A B8010000 8C01 0000	map leaf, size 10: @1B8 → @18C
1CA	6C 77696E646F77	txt "window"
1D1	0F 0A CA010000 6401 0000	map leaf, size 10: @1CA → @164
1DB	07 16 2A400000 9F01 0000 AE010000 C001 0000 D1010000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 14: @19F → @1AE → @1C0 → @1D1
1F1	DB010000 00000000	root → @1DB; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	7C 72756E6E696E67	txt "running"
00C	02 78000000000000 00	i64 120
015	0F 0A 04000000 0C00 0000	map leaf, size 10: @4 → @C
01F	4C 6E616D65	txt "name"
024	3C 416461	txt "Ada"
028	0F 0A 1F000000 2400 0000	map leaf, size 10: @1F → @24
032	07 0E 40800000 1500 0000 28000000	map branch, size 14, depth 0 (root), slots 6, 15: @15 → @28
040	7C 72756E6E696E67	txt "running"
048	02 DC000000000000 00	i64 220
051	0F 0A 40000000 4800 0000	map leaf, size 10: @40 → @48
05B	4C 6E616D65	txt "name"
060	3C 426F62	txt "Bob"
064	0F 0A 5B000000 6000 0000	map leaf, size 10: @5B → @60
06E	07 0E 40800000 5100 0000 64000000	map branch, size 14, depth 0 (root), slots 6, 15: @51 → @64
07C	7C 72756E6E696E67	txt "running"
084	02 36010000000000 00	i64 310
08D	0F 0A 7C000000 8400 0000	map leaf, size 10: @7C → @84
097	4C 6E616D65	txt "name"
09C	2C 4379	txt "Cy"
09F	0F 0A 97000000 9C00 0000	map leaf, size 10: @97 → @9C
0A9	07 0E 40800000 8D00 0000 9F000000	map branch, size 14, depth 0 (root), slots 6, 15: @8D → @9F
0B7	7C 72756E6E696E67	txt "running"
0BF	02 86010000000000 00	i64 390
0C8	0F 0A B7000000 BF00 0000	map leaf, size 10: @B7 → @BF
0D2	4C 6E616D65	txt "name"
0D7	3C 457665	txt "Eve"
0DB	0F 0A D2000000 D700 0000	map leaf, size 10: @D2 → @D7
0E5	07 0E 40800000 C800 0000 DB000000	map branch, size 14, depth 0 (root), slots 6, 15: @C8 → @DB
0F3	7C 72756E6E696E67	txt "running"
0FB	02 CC010000000000 00	i64 460
104	0F 0A F3000000 FB00 0000	map leaf, size 10: @F3 → @FB
10E	4C 6E616D65	txt "name"
113	3C 44616E	txt "Dan"
117	0F 0A 0E010000 1301 0000	map leaf, size 10: @10E → @113
121	07 0E 40800000 0401 0000 17010000	map branch, size 14, depth 0 (root), slots 6, 15: @104 → @117
12F	0E 1D 00 1F00 050000 00 32000000 6E0000 00 A9000000 E50000 00 21010000	array leaf, size 29, shift 0, bitmap 001F, count 5 (root): @32 → @6E → @A9 → @E5 → @121
14C	2F010000 00000000	root → @12F; previous: none

Without a frame, a function reads the full partition, also when the window has an orderBy. from must be unboundedPreceding. to is currentRow or unboundedFollowing.

First, last and nth values#

The readers give a value from a row in the frame. firstComponent reads the first row, lastComponent reads the last row, and nthComponent reads the row at a position. The first position is 1. A position after the end of the frame gives null. This query reads through the current row. Thus, lastComponent gives the current name. nthComponent gives null on the first row.

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 3F6E616D65	txt "?name"
00A	6C 3F6669727374	txt "?first"
011	5C 3F6C617374	txt "?last"
017	7C 3F7365636F6E64	txt "?second"
01F	0E 19 00 0F00 040000 00 04000000 0A0000 00 11000000 170000 00	array leaf, size 25, shift 0, bitmap 000F, count 4 (root): @4 → @A → @11 → @17
038	2C 3F65	txt "?e"
03B	6C 73616C617279	txt "salary"
042	4C 3F73616C	txt "?sal"
047	0E 15 00 0700 030000 00 38000000 3B0000 00 42000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @38 → @3B → @42
05C	2C 3F65	txt "?e"
05F	4C 6E616D65	txt "name"
064	5C 3F6E616D65	txt "?name"
06A	0E 15 00 0700 030000 00 5C000000 5F0000 00 64000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @5C → @5F → @64
07F	0E 11 00 0300 020000 00 47000000 6A0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @47 → @6A
090	EC 6669727374436F 6D706F6E656E74	txt "firstComponent"
09F	5C 3F6E616D65	txt "?name"
0A5	0E 11 00 0300 020000 00 90000000 9F0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @90 → @9F
0B6	4C 3F73616C	txt "?sal"
0BB	4C 64657363	txt "desc"
0C0	0E 11 00 0300 020000 00 B6000000 BB0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @B6 → @BB
0D1	0E 0D 00 0100 010000 00 C0000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @C0
0DE	4C 66726F6D	txt "from"
0E3	14 12 756E626F756E 6465645072656365 64696E67	txt "unboundedPreceding"
0F7	0F 0A DE000000 E300 0000	map leaf, size 10: @DE → @E3
101	2C 746F	txt "to"
104	AC 63757272656E74 526F77	txt "currentRow"
10F	0F 0A 01010000 0401 0000	map leaf, size 10: @101 → @104
119	07 0E 00900000 F700 0000 0F010000	map branch, size 14, depth 0 (root), slots 12, 15: @F7 → @10F
127	4C 726F7773	txt "rows"
12C	0F 0A 27010000 1901 0000	map leaf, size 10: @127 → @119
136	5C 6672616D65	txt "frame"

Show all 98 nodes

Offset (hex)	Bytes (hex)	Meaning
13C	0F 0A 36010000 2C01 0000	map leaf, size 10: @136 → @12C
146	7C 6F726465724279	txt "orderBy"
14E	0F 0A 46010000 D100 0000	map leaf, size 10: @146 → @D1
158	07 0E 30000000 3C01 0000 4E010000	map branch, size 14, depth 0 (root), slots 4, 5: @13C → @14E
166	6C 3F6669727374	txt "?first"
16D	0E 15 00 0700 030000 00 A5000000 660100 00 58010000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @A5 → @166 → @158
182	DC 6C617374436F6D 706F6E656E74	txt "lastComponent"
190	5C 3F6E616D65	txt "?name"
196	0E 11 00 0300 020000 00 82010000 900100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @182 → @190
1A7	4C 3F73616C	txt "?sal"
1AC	4C 64657363	txt "desc"
1B1	0E 11 00 0300 020000 00 A7010000 AC0100 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @1A7 → @1AC
1C2	0E 0D 00 0100 010000 00 B1010000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @1B1
1CF	4C 66726F6D	txt "from"
1D4	14 12 756E626F756E 6465645072656365 64696E67	txt "unboundedPreceding"
1E8	0F 0A CF010000 D401 0000	map leaf, size 10: @1CF → @1D4
1F2	2C 746F	txt "to"
1F5	AC 63757272656E74 526F77	txt "currentRow"
200	0F 0A F2010000 F501 0000	map leaf, size 10: @1F2 → @1F5
20A	07 0E 00900000 E801 0000 00020000	map branch, size 14, depth 0 (root), slots 12, 15: @1E8 → @200
218	4C 726F7773	txt "rows"
21D	0F 0A 18020000 0A02 0000	map leaf, size 10: @218 → @20A
227	5C 6672616D65	txt "frame"
22D	0F 0A 27020000 1D02 0000	map leaf, size 10: @227 → @21D
237	7C 6F726465724279	txt "orderBy"
23F	0F 0A 37020000 C201 0000	map leaf, size 10: @237 → @1C2
249	07 0E 30000000 2D02 0000 3F020000	map branch, size 14, depth 0 (root), slots 4, 5: @22D → @23F
257	5C 3F6C617374	txt "?last"
25D	0E 15 00 0700 030000 00 96010000 570200 00 49020000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @196 → @257 → @249
272	CC 6E7468436F6D70 6F6E656E74	txt "nthComponent"
27F	5C 3F6E616D65	txt "?name"
285	02 02000000000000 00	i64 2
28E	0E 15 00 0700 030000 00 72020000 7F0200 00 85020000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @272 → @27F → @285
2A3	4C 3F73616C	txt "?sal"
2A8	4C 64657363	txt "desc"
2AD	0E 11 00 0300 020000 00 A3020000 A80200 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @2A3 → @2A8
2BE	0E 0D 00 0100 010000 00 AD020000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @2AD
2CB	4C 66726F6D	txt "from"
2D0	14 12 756E626F756E 6465645072656365 64696E67	txt "unboundedPreceding"
2E4	0F 0A CB020000 D002 0000	map leaf, size 10: @2CB → @2D0
2EE	2C 746F	txt "to"
2F1	AC 63757272656E74 526F77	txt "currentRow"
2FC	0F 0A EE020000 F102 0000	map leaf, size 10: @2EE → @2F1
306	07 0E 00900000 E402 0000 FC020000	map branch, size 14, depth 0 (root), slots 12, 15: @2E4 → @2FC
314	4C 726F7773	txt "rows"
319	0F 0A 14030000 0603 0000	map leaf, size 10: @314 → @306
323	5C 6672616D65	txt "frame"
329	0F 0A 23030000 1903 0000	map leaf, size 10: @323 → @319
333	7C 6F726465724279	txt "orderBy"
33B	0F 0A 33030000 BE02 0000	map leaf, size 10: @333 → @2BE
345	07 0E 30000000 2903 0000 3B030000	map branch, size 14, depth 0 (root), slots 4, 5: @329 → @33B
353	7C 3F7365636F6E64	txt "?second"
35B	0E 15 00 0700 030000 00 8E020000 530300 00 45030000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @28E → @353 → @345
370	0E 15 00 0700 030000 00 6D010000 5D0200 00 5B030000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @16D → @25D → @35B
385	4C 3F73616C	txt "?sal"
38A	4C 64657363	txt "desc"
38F	0E 11 00 0300 020000 00 85030000 8A0300 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @385 → @38A
3A0	0E 0D 00 0100 010000 00 8F030000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @38F
3AD	5C 7768657265	txt "where"
3B3	0F 0A AD030000 7F00 0000	map leaf, size 10: @3AD → @7F
3BD	4C 66696E64	txt "find"
3C2	0F 0A BD030000 1F00 0000	map leaf, size 10: @3BD → @1F
3CC	7C 6F726465724279	txt "orderBy"
3D4	0F 0A CC030000 A003 0000	map leaf, size 10: @3CC → @3A0
3DE	6C 77696E646F77	txt "window"
3E5	0F 0A DE030000 7003 0000	map leaf, size 10: @3DE → @370
3EF	07 16 2A400000 B303 0000 C2030000 D403 0000 E5030000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 14: @3B3 → @3C2 → @3D4 → @3E5

Offset (hex)	Bytes (hex)	Meaning
405	EF030000 00000000	root → @3EF; previous: none

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	4C 6C617374	txt "last"
009	3C 416461	txt "Ada"
00D	0F 0A 04000000 0900 0000	map leaf, size 10: @4 → @9
017	5C 6669727374	txt "first"
01D	3C 416461	txt "Ada"
021	0F 0A 17000000 1D00 0000	map leaf, size 10: @17 → @1D
02B	6C 7365636F6E64	txt "second"
032	00	nil
033	0F 0A 2B000000 3200 0000	map leaf, size 10: @2B → @32
03D	4C 6E616D65	txt "name"
042	3C 416461	txt "Ada"
046	0F 0A 3D000000 4200 0000	map leaf, size 10: @3D → @42
050	07 16 05A00000 0D00 0000 21000000 3300 0000 46000000	map branch, size 22, depth 0 (root), slots 0, 2, 13, 15: @D → @21 → @33 → @46
066	4C 6C617374	txt "last"
06B	3C 426F62	txt "Bob"
06F	0F 0A 66000000 6B00 0000	map leaf, size 10: @66 → @6B
079	5C 6669727374	txt "first"
07F	3C 416461	txt "Ada"
083	0F 0A 79000000 7F00 0000	map leaf, size 10: @79 → @7F
08D	6C 7365636F6E64	txt "second"
094	3C 426F62	txt "Bob"
098	0F 0A 8D000000 9400 0000	map leaf, size 10: @8D → @94
0A2	4C 6E616D65	txt "name"
0A7	3C 426F62	txt "Bob"
0AB	0F 0A A2000000 A700 0000	map leaf, size 10: @A2 → @A7
0B5	07 16 05A00000 6F00 0000 83000000 9800 0000 AB000000	map branch, size 22, depth 0 (root), slots 0, 2, 13, 15: @6F → @83 → @98 → @AB
0CB	4C 6C617374	txt "last"
0D0	2C 4379	txt "Cy"
0D3	0F 0A CB000000 D000 0000	map leaf, size 10: @CB → @D0
0DD	5C 6669727374	txt "first"
0E3	3C 416461	txt "Ada"

Show all 66 nodes

Offset (hex)	Bytes (hex)	Meaning
0E7	0F 0A DD000000 E300 0000	map leaf, size 10: @DD → @E3
0F1	6C 7365636F6E64	txt "second"
0F8	3C 426F62	txt "Bob"
0FC	0F 0A F1000000 F800 0000	map leaf, size 10: @F1 → @F8
106	4C 6E616D65	txt "name"
10B	2C 4379	txt "Cy"
10E	0F 0A 06010000 0B01 0000	map leaf, size 10: @106 → @10B
118	07 16 05A00000 D300 0000 E7000000 FC00 0000 0E010000	map branch, size 22, depth 0 (root), slots 0, 2, 13, 15: @D3 → @E7 → @FC → @10E
12E	4C 6C617374	txt "last"
133	3C 457665	txt "Eve"
137	0F 0A 2E010000 3301 0000	map leaf, size 10: @12E → @133
141	5C 6669727374	txt "first"
147	3C 416461	txt "Ada"
14B	0F 0A 41010000 4701 0000	map leaf, size 10: @141 → @147
155	6C 7365636F6E64	txt "second"
15C	3C 426F62	txt "Bob"
160	0F 0A 55010000 5C01 0000	map leaf, size 10: @155 → @15C
16A	4C 6E616D65	txt "name"
16F	3C 457665	txt "Eve"
173	0F 0A 6A010000 6F01 0000	map leaf, size 10: @16A → @16F
17D	07 16 05A00000 3701 0000 4B010000 6001 0000 73010000	map branch, size 22, depth 0 (root), slots 0, 2, 13, 15: @137 → @14B → @160 → @173
193	4C 6C617374	txt "last"
198	3C 44616E	txt "Dan"
19C	0F 0A 93010000 9801 0000	map leaf, size 10: @193 → @198
1A6	5C 6669727374	txt "first"
1AC	3C 416461	txt "Ada"
1B0	0F 0A A6010000 AC01 0000	map leaf, size 10: @1A6 → @1AC
1BA	6C 7365636F6E64	txt "second"
1C1	3C 426F62	txt "Bob"
1C5	0F 0A BA010000 C101 0000	map leaf, size 10: @1BA → @1C1
1CF	4C 6E616D65	txt "name"
1D4	3C 44616E	txt "Dan"
1D8	0F 0A CF010000 D401 0000	map leaf, size 10: @1CF → @1D4
1E2	07 16 05A00000 9C01 0000 B0010000 C501 0000 D8010000	map branch, size 22, depth 0 (root), slots 0, 2, 13, 15: @19C → @1B0 → @1C5 → @1D8
1F8	0E 1D 00 1F00 050000 00 50000000 B50000 00 18010000 7D0100 00 E2010000	array leaf, size 29, shift 0, bitmap 001F, count 5 (root): @50 → @B5 → @118 → @17D → @1E2

Offset (hex)	Bytes (hex)	Meaning
215	F8010000 00000000	root → @1F8; previous: none

Without the frame, every row gets last Dan, because the function reads the full result.

Window function names#

These are the window function names:

The position names and the readers need an orderBy in the window configuration. lag and lead accept a value, an optional offset, and an optional fallback. The offset is 1 by default and must not be negative. nthComponent accepts a value and a position from 1. The other readers and the numbers accept one value.

Run it#

database query · query run.