Data Preparation II: Window Functions, Granularity

February 27, 2025

1

Data 101, Spring 2025 @ UC Berkeley

Aditya Parameswaran https://data101.org/sp25

​

​

LECTURE 12

Do not edit

How to change the design

Join at slido.com�#4795043

Presenting with animations, GIFs or speaker notes? Enable our Chrome extension

Data Value Transformations

After you’ve restructured your data, you will likely need to transform values.

• We’ve seen this referred to as part of data wrangling or data cleaning.
• I personally don’t like “cleaning”—it implies our original data were dirty.
• But as we’ve seen, some value inconsistency may simply arise from structural transformations!

​

There are many value transformations.

• String transformations (briefly last time)
• Numeric calculations

3

4795043

Data Value Transformations

After you’ve restructured your data, you will likely need to transform values.

• We’ve seen this referred to as part of data wrangling or data cleaning.
• I personally don’t like “cleaning”—it implies our original data were dirty.
• But as we’ve seen, some value inconsistency may simply arise from structural transformations!

There are many value transformations.

• String transformations (briefly last time)
• Numeric calculations

There are any number of reasons to transform data via numerical calculation.

Typical calculations:

• Scalar functions
• Aggregate functions
• Window functions: basic, inverse-distribution, hypothetical-set

Calculations look fairly similar across languages. Let’s focus on SQL.

4

4795043

Scalar

Functions

Scalar Functions

Aggregate Functions

Window Functions

Granularity Transformations

Numerical Granularity

Hierarchical Granularity

Rollup/Drilldown constructs

Implementing Explicit Hierarchies

5

Lecture 12, Data 101, Spring 2025

Scalar Functions

A scalar function is a function on atomic values.

• In the relational model, this is a function on constants�and individual attributes of a single relational tuple.

​

Etymology note: You have likely seen the word “scalar” in different contexts:

• A scalar is tensor of dimension 0; i.e., a scalar is a value in a numeric field.
• Colloquially, a scalar is used to represent a single value in any atomic data type, e.g., integer, string, float, …,

​

As part of our definition of scalar functions, we combine these to define a scalar as a value for any individual attribute.

6

4795043

Exercise: How many scalar functions?

7

WITH year_num AS

(SELECT year_id, (year_id % 100) AS year

FROM batting

)

SELECT year_id,

CONCAT('''', LPAD(year::text, 2, '0')) AS year

FROM year_num

LIMIT 5;

​

LPAD [docs 16.2 link]:

lpad ( string text, length integer [, fill text ] ) → text

Extends the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right).

lpad('hi', 5, 'xy') → xyxhi

'''' ← postgres escape (character ') of single quote '

value::type ← postgres-specific typecast syntax

🤔

A. 0

B. 1

C. 2

D. 3

E. 4

F. Something else

4795043

Exercise: How many scalar functions?

8

Scalar functions are computed individually on each record in your data.

WITH year_num AS

(SELECT year_id, (year_id % 100) AS year

FROM batting

)

SELECT year_id,

CONCAT('''', LPAD(year::text, 2, '0')) AS year

FROM year_num

LIMIT 5;

​

A. 0

B. 1

C. 2

D. 3

E. 4

F. Something else

4795043

Side note: Scalar Functions and Parallelism

Scalar functions are computed individually on each record in your data.

• In relational algebra, use a scalar function f in a projection operator, e.g.,
• In query-based languages like SQL, put in SELECT clause
• In code-based/dataflow libraries like Spark and Pandas, invoke via map function

​

Because it operates per-record, the processing engine has several options for efficiency:

• Run these functions in parallel on many different records
• Free up memory from one invocation before starting the other (memory reuse)
• Use pipelining to execute scalar functions “on-the-fly” as tuples are emitted, e.g., while accessing the data via a Sequential/Index Scan

9

4795043

Let’s check it out

(On your own)

A “flattening” of the query above (to remove the CTE):

EXPLAIN (VERBOSE true)�SELECT year_id,� CONCAT('''', LPAD((year_id % 100)::text, 2, '0')) AS year�FROM batting;

​

10

4795043

Demo

What if scalar functions mention multiple tables?

(On your own)

11

The below query computes an arbitrary statistic for pitchers:

• 1 point for every strikeout they throw as pitcher
• –1 for every point they themselves struck out as batter

EXPLAIN (VERBOSE true)�SELECT p.player_id, p.so - b.so� FROM pitching p� INNER JOIN batting b� ON p.playerid=b.player_id;

Scalar functions are computed as each tuple is output from the join.

Demo

What if scalar functions mention multiple tables?

12

The below query computes an arbitrary statistic for pitchers:

• 1 point for every strikeout they throw as pitcher
• –1 for every point they themselves struck out as batter

EXPLAIN (VERBOSE true)�SELECT p.player_id, p.so - b.so�FROM pitching p JOIN batting b� ON p.player_id=b.player_id;

Nested Loop (cost=0.43..13004.27 rows=339358 width=13)

Output: p.player_id, (p.so - b.so)

-> Seq Scan on public.pitching p (cost=0.00..1374.06 rows=45806 width=13)

Output: p.playerid, p.yearid, p.stint, p.teamid, p.lgid, p.w, p.l, p.g, p.gs, p.cg, p.sho, p.sv, p.ipouts, p.h, p.er, p.hr, p.bb, p.so, p.baopp, p.era, p.ibb, p.wp, p.hbp, p.bk, p.bfp, p.gf, p.r, p.sh, p.sf, p.gidp

-> Memoize (cost=0.43..0.73 rows=7 width=13)

Output: b.so, b.playerid

Cache Key: p.playerid

Cache Mode: logical

-> Index Scan using batting_pkey on public.batting b (cost=0.42..0.72 rows=7 width=13)

Output: b.so, b.playerid

Index Cond: ((b.playerid)::text = (p.playerid)::text)

(11 rows)

​

(using psql directly, which preserves whitespace formatting)

Demo

[Bonus] User-Defined Functions (UDFs)

Some DBMSes allow you to define scalar functions of your own.

• Postgres supports Python…but be warned.
• Not available on DataHub
• Set it up on your own Postgres server [link]:

​

%sql CREATE EXTENSION plpython3u;

%%sql

CREATE OR REPLACE FUNCTION pyhash(s text)

RETURNS text

AS $$

## Python text goes here, can reference variable s

import hashlib

m = hashlib.sha256()

m.update(s) # uses variable s

return m.hexdigest() # return a text

$$ LANGUAGE plpython3u;

​

%sql SELECT pyhash('Joe'), pyhash('Joel');

13

Demo

Aggregate Functions

Scalar Functions

Aggregate Functions

Window Functions

Granularity Transformations

Numerical Granularity

Hierarchical Granularity

Rollup/Drilldown constructs

Implementing Explicit Hierarchies

14

Lecture 12, Data 101, Spring 2025

Aggregate Functions

Aggregate Functions take a set or vector of values as their input.

• SQL, Pandas, Excel formula language: family of aggregate functions

PostgreSQL Documentation §9.2

• Univariate functions on sets of numbers: min, max, sum, avg, stddev, variance, etc.
• Bivariate functions on sets of numbers: various forms of correlation (e.g. corr),�covariance (e.g. covar_samp), regression (e.g. regr_sxx), etc,
• Univariate functions on ordered lists of numbers: percentile_disc
• Univariate functions on lists of strings:
• string_agg concatenate all values
• array_agg converts the list of values into an array
• json_agg converts the list into a JSON object

15

4795043

Aggregate Functions and Performance

(We’ve discussed this before, but just to be comprehensive:)

Aggregate functions typically will not return an answer�until they've taken a full pass on the data!

• This blocking makes aggregations time-consuming for big datasets.

Possible speed-ups?

• Table samples…but have to tweak parameters to get statistically meaningful results.

​

Slow performance in aggregate functions is especially complicated for queries with joins. (more later)

​

16

4795043

Example: Annual Home Run statistics

(On your own)

EXPLAIN (VERBOSE true)�SELECT name_first, name_last, year_id,� MIN(hr), MAX(hr), AVG(hr), STDDEV(hr), SUM(hr)�FROM batting b, people p�WHERE b.player_id = p.player_id�GROUP BY name_last, name_first, year_id�ORDER BY max DESC�LIMIT 10;

​

17

4795043

Demo Code

Example: Annual Home Run statistics EXPLAIN

18

Limit (cost=17858.93..17858.95 rows=10 width=96)

Output: p.namefirst, p.namelast, b.yearid, (min(b.hr)), (max(b.hr)), � (avg(b.hr)), (stddev(b.hr)), (sum(b.hr))

-> Sort (cost=17858.93..18119.74 rows=104324 width=96)

Output: p.namefirst, p.namelast, b.yearid, (min(b.hr)), � (max(b.hr)), (avg(b.hr)), (stddev(b.hr)), (sum(b.hr))

Sort Key: (max(b.hr)) DESC

-> HashAggregate (cost=12817.12..15604.53 rows=104324 width=96)

Output: p.namefirst, p.namelast, b.yearid, min(b.hr), � max(b.hr), avg(b.hr), stddev(b.hr), sum(b.hr)

Group Key: p.namelast, p.namefirst, b.yearid

Planned Partitions: 8

-> Hash Join (cost=861.83..3753.97 rows=104324 width=20)

Output: p.namefirst, p.namelast, b.yearid, b.hr

Inner Unique: true

Hash Cond: ((b.playerid)::text = (p.playerid)::text)

-> Seq Scan on public.batting b (cost=0.00..2618.24 � rows=104324 width=17)

Output: b.playerid, b.yearid, b.stint, b.teamid, � b.lgid, b.g, b.ab, b.r, b.h, b.h2b, � b.h3b, b.hr, b.rbi, b.sb, b.cs, b.bb, � b.so, b.ibb, b.hbp, b.sh, b.sf, b.gidp

-> Hash (cost=619.70..619.70 rows=19370 width=21)

Output: p.namefirst, p.namelast, p.playerid

-> Seq Scan on public.people p � (cost=0.00..619.70 rows=19370 width=21)

Output: p.namefirst, p.namelast, p.playerid

(19 rows)

(using psql directly, which preserves whitespace formatting)

Demo Code

Do not edit

How to change the design

How is Project 2 going?

Presenting with animations, GIFs or speaker notes? Enable our Chrome extension

Window Functions

Scalar Functions

Aggregate Functions

Window Functions

• Partition
• Order
• Range

Granularity Transformations

Numerical Granularity

Hierarchical Granularity

Rollup/Drilldown constructs

Implementing Explicit Hierarchies

20

Lecture 12, Data 101, Spring 2025

Window Functions

Window functions consider rows beyond the current row (a window) in the calculation.

• Without collapsing rows into groups; instead, rows retain their separate identities.

This is a generally useful primitive in data science with several applications:

• Normalize a certain attribute using a group average, e.g., sliding window
• Compute a cumulative sum, e.g., total amount earned
• Compare a given attribute when the previous value, e.g., daily change
• Compute rank

21

[3.5, 4.2.8, 9.22]

4795043

Window Functions

NB: Conceptually evaluated after GROUP BY/HAVING

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

22

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

[3.5, 4.2.8, 9.22]

4795043

1. Compute a function

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

Could contain typical aggregate functions: AVG, SUM, …

As well as specific window functions, e.g.,

• RANK() ordering within the window
• LEAD/LAG(exp, n) value of exp that is n rows ahead or � n behind in current window
• PERCENT_RANK() relative rank of current row � as a percentage [0, 1]
• NTH_VALUE(exp, n) value of exp that is at position � n in the window

23

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

​

​

4795043

1. Compute a function, Example

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

24

SELECT id, location, age,

AVG(age) OVER ()

AS avg_age

FROM Stops;

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

AVG is computed over the entire table (i.e., entire table is the partition).

4795043

2. Define a partition, i.e., a window

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

25

SELECT id, location, age,

AVG(age) OVER � (PARTITION BY location)

AS avg_age

FROM Stops;

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

​

​

AVG is computed over each location partition.

Documentation 3.5, 4.2.8, 9.22

Baseball DB

(Baseball DB)

SELECT year_id, player_id, team_id, salary

FROM salaries

ORDER BY year_id DESC, salary DESC

LIMIT 5;

​

SELECT year_id, player_id, team_id, salary, AVG(salary) OVER()

FROM salaries

ORDER BY year_id DESC, salary DESC

LIMIT 5;

26

Demo Code

3. Order tuples within a partition / window

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

27

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

​

​

SELECT id, location, age,

RANK() OVER (

PARTITION BY location

ORDER BY age)

AS a_rank FROM Stops

ORDER BY location, a_rank

RANK is computed per row after partition by location, then ordering.

Documentation 3.5, 4.2.8, 9.22

Baseball DB

(Baseball DB)

SELECT year_id, player_id, team_id, salary,

RANK() OVER(

PARTITION BY year_id, team_id

ORDER BY salary DESC

) as salary_rank

FROM salaries

ORDER BY year_id DESC, salary DESC

LIMIT 5;

​

28

Demo Code

3. Order tuples within a partition / window

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

29

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

​

​

SELECT id, location, age,

RANK() OVER (

ORDER BY age)

AS a_rank FROM Stops

ORDER BY a_rank

RANK is computed per row after null op partition, then ordering.

When PARTITION is not specified, the window function is taken over the entire table.

Documentation 3.5, 4.2.8, 9.22

Baseball DB

SELECT year_id, player_id, team_id, salary,

RANK() OVER(

ORDER BY salary DESC

) as salary_rank

FROM salaries

ORDER BY salary DESC

LIMIT 5;

​

30

Demo Code

Window Functions so far

<window or agg_func> OVER (

1. Compute a function

[PARTITION BY <…>]

2. over a particular window� (Partition into windows the larger group of � rows that the current row is part of)

[ORDER BY <…>]

3. where the window tuples are ordered� (Define the way this partition (window) is� laid out)

31

4795043

4. RANGE

<window or agg_func> OVER (

1. Compute a function

[PARTITION BY <…>]

2. over a particular window� (Partition into windows the larger group of � rows that the current row is part of)

[ORDER BY <…>]

3. where the window tuples are ordered� (Define the way this partition (window) is� laid out)

32

[RANGE BETWEEN <…> AND <…>])

4. and take this particular frame of � interest in the window� (the subset within this ordered partition to � compare against)

4795043

4. Select a range / frame of interest within the partition

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

33

SELECT id, location, age, � SUM(age) OVER (

PARTITION BY location

ORDER BY age

RANGE BETWEEN� UNBOUNDED PRECEDING AND � 1 PRECEDING )

AS a_sum

FROM Stops

ORDER BY location, age

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

range_start/range_end general syntax:

UNBOUNDED PRECEDING

UNBOUNDED FOLLOWING

CURRENT ROW

offset PRECEDING

offset FOLLOWING

range_start

range_end

4795043

4. Select a range / frame of interest within the partition

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

34

Compute sum of all preceding ages per partition

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

SELECT id, location, age, � SUM(age) OVER (

PARTITION BY location

ORDER BY age

RANGE BETWEEN� UNBOUNDED PRECEDING AND � 1 PRECEDING )

AS a_sum

FROM Stops

ORDER BY location, age

Documentation 3.5, 4.2.8, 9.22

OB: 30, 35, 40

PB: 30, 40, 35

4795043

4. Select a range / frame of interest within the partition

<window or agg_func> OVER (

[PARTITION BY <…>]

[ORDER BY <…>]

[RANGE BETWEEN <…> AND <…>])

35

SELECT id, location, age, � SUM(age) OVER (

PARTITION BY location

ORDER BY age

RANGE BETWEEN� UNBOUNDED PRECEDING AND � 1 PRECEDING )

AS a_sum

FROM Stops

ORDER BY location, age

1. compute a function

2. over a particular window

3. where the window tuples are ordered like so

4. and take this particular frame of interest in the window

Documentation 3.5, 4.2.8, 9.22

range_start/range_end general syntax:

UNBOUNDED PRECEDING

UNBOUNDED FOLLOWING

CURRENT ROW

offset PRECEDING

offset FOLLOWING

In general, RANGE:

• Can compute cumulative sums
• Can compare current value with prev window of values

Very useful in temporal contexts!

range_start

range_end

4795043

Exercise/Demo Window Function

36

Lecture 12, Data 101, Spring 2025

Which of the below exercises do you want to walk through?

37

SELECT title_id, name, title,

LENGTH(name),

RANK() OVER (PARTITION BY title ORDER BY LENGTH(name) DESC)

AS name_rank

FROM actor_title

WHERE title LIKE 'A %'

ORDER BY title, name_rank;

SELECT title_id, name, title,

AVG(LENGTH(name)) OVER (PARTITION BY title)

AS avg_name_length

FROM actor_title

WHERE title LIKE 'The %'

ORDER BY title;

​

SELECT title_id, name, title,

CAST(AVG(LENGTH(name)) OVER (PARTITION BY title)

AS INTEGER)

AS avg_name_length

FROM actor_title

WHERE title LIKE 'The %'

ORDER BY title;

A. �

B. �

C. �

actor_title

Column | Type

----------+------

title | text

title_id | text

name | text

​

(Created from adding title_id to crew, and filtering for actors only)

🤔

4795043

Which of the below exercises do you want to walk through?

Compute average actor name length per movie

Compute average actor name length per movie, but return integer

Compute ranks of lengths of actor names per movie

38

SELECT title_id, name, title,

LENGTH(name),

RANK() OVER (PARTITION BY title ORDER BY LENGTH(name) DESC)

AS name_rank

FROM actor_title

WHERE title LIKE 'A %'

ORDER BY title, name_rank;

SELECT title_id, name, title,

AVG(LENGTH(name)) OVER (PARTITION BY title)

AS avg_name_length

FROM actor_title

WHERE title LIKE 'The %'

ORDER BY title;

​

SELECT title_id, name, title,

CAST(AVG(LENGTH(name)) OVER (PARTITION BY title)

AS INTEGER)

AS avg_name_length

FROM actor_title

WHERE title LIKE 'The %'

ORDER BY title;

A. �

B. �

C. �

4795043

Optional Practice: Windows and Casting

• Compute average actor name length per title
• SELECT id, name, title, avg(length(name)) OVER (PARTITION BY title) as avg_length FROM actor_title WHERE title like 'The %' ORDER BY title
• Cast as integer:

SELECT id, name, title, CAST (avg(length(name)) OVER (PARTITION BY title) AS INTEGER) as avg_length FROM actor_title WHERE title like ‘The %' ORDER BY title

• Compute ranks of lengths of actor names per title
• SELECT id, name, title, rank() OVER (PARTITION BY title ORDER BY length(name) DESC) as name_rank FROM actor_title WHERE title like 'A %' ORDER BY title, name_rank

​

​

39

4795043

Window Functions, Extended

So far: 1 value in output for each "window" of input values

1. Any aggregate function can be used in a window!
• Order-based aggregates (thanks to ordered windows).
2. (new) Inverse Distribution Window Functions
3. (new) Hypothetical-Set Window Functions

​

40

4795043

(1/3) Window Functions (Aggregate)

4-year windowed statistics on home runs for Bonds and Ruth:

​

SELECT name_first, name_last, year_id, hr as ‘home runs’,

rank() OVER (ORDER BY hr DESC),

avg(hr) OVER (PARTITION BY b.player_id� ORDER BY year_id ROWS 3 PRECEDING)� AS avg_4yr,

lag(hr, 1) OVER (PARTITION BY b.player_id ORDER BY year_id) AS previous,

lag(hr, 2) OVER (PARTITION BY b.player_id ORDER BY year_id) AS lag2

FROM batting b, people p

WHERE p.player_id = b.player_id

AND (name_last = 'Bonds' or name_last = 'Ruth')

ORDER BY hr DESC

LIMIT 10;

​

41

NB: avg_4yr averages 4 rows (including current row—the range_end default)

4795043

(2/3) Inverse Distribution Window Functions

The value at a particular "position" in a distribution

• SQL: use WITHIN GROUP (ORDER BY...).

Example below:

• "Tukey numbers" (min, quartiles, max, avg) for home runs
• plus "p99" for home runs (since homeruns/year is heavy-tailed distribution)

​

SELECT MIN(HR),

percentile_cont(0.25) WITHIN GROUP (ORDER BY HR) AS p25,

percentile_cont(0.50) WITHIN GROUP (ORDER BY HR) AS median,

percentile_cont(0.75) WITHIN GROUP (ORDER BY HR) AS p75,

percentile_cont(0.99) WITHIN GROUP (ORDER BY HR) AS p99,

MAX(HR),

AVG(HR)

FROM batting

LIMIT 10;

42

4795043

(3/3) Hypothetical-Set Window Functions

The position of a value in a distribution *even if the value wasn't in the data*

• PostgreSQL: rank, dense_rank, percent_rank and cume_dist

Example: Would I be in the top 0.1% if I hit 4 homeruns?

​

​

SELECT 4 as hypothetical,

rank(4) WITHIN GROUP (ORDER BY HR DESC),

dense_rank(4) WITHIN GROUP (ORDER BY HR DESC),

percent_rank(4) WITHIN GROUP (ORDER BY HR DESC) * 100 AS pct_rank,

cume_dist(4) WITHIN GROUP (ORDER BY HR)

FROM batting

LIMIT 10;

43

4795043

Granularity Transformations

Scalar Functions

Aggregate Functions

Window Functions

Granularity Transformations

Numerical Granularity

Hierarchical Granularity

Rollup/Drilldown constructs

Implementing Explicit Hierarchies

44

Lecture 12, Data 101, Spring 2025

Why granularity transformations?

Data is often recorded and/or released at different granularities. Two concepts:

45

Granularity transformation is transforming data by “rolling up” to coarser granularity, or “drilling down” to finer granularity.

Data hierarchy:

Numerical data is measured in a hierarchy of units:

• seconds → hours → minutes → days
• mm → cm → m → km
• in → ft → mi

Non-numerical hierarchies also exist!

• car → vehicle → thing
• species → genus → … → kingdom → domain

Discretization of numerical granularity: Measurement inherently begets granularity.

• Continuous value in the physical world is discretized (“rounded” or quantized)�into a digital measurement.
• Encoded as a fixed number of bits.
• Approximation/truncation due to physical device capabilities and digitization (see signal proc.).

4795043

New dataset: GNIS

GNIS: Geographic Names Information Systems

• U.S. federal/national standard for geographic nomenclature
• https://www.usgs.gov/core-science-systems/ngp/board-on-geographic-names/download-gnis-data
• The file [direct zip download]:

46

Table "public.national"

Column | Type

-----------------+------------------

feature_id | bigint

feature_name | text

feature_class | text

state_alpha | text

state_numeric | bigint

county_name | text

county_numeric | double precision

primary_lat_dms | text

prim_long_dms | text

prim_lat_dec | double precision

prim_long_dec | double precision

source_lat_dms | text

source_long_dms | text

source_lat_dec | double precision

source_long_dec | double precision

elev_in_m | double precision

elev_in_ft | double precision

map_name | text

date_created | text

​

4795043

Numerical Granularity

Scalar Functions

Aggregate Functions

Window Functions

Granularity Transformations

Numerical Granularity

Hierarchical Granularity

Rollup/Drilldown constructs

Implementing Explicit Hierarchies

47

Lecture 12, Data 101, Spring 2025

Numerical Granularity

Effectively, manipulate the number of “significant digits.” A few techniques:

• Rounding or closest power of 10
• Bitwise arithmetic operations

48

4795043

Quantize with bitshifts

• Can also quantize n-bit numbers to fewer bits.
• Common in Deep Learning pipelines.

Each bit position is a power of two.

• Shifting b bits right (>> b) divides by 2^b, drops bits.
• Shifting b bits left (<< n) multiplies by 2^b.
• Shifting right-then-left by b
• Keep only the leading n-b bits
• Pad the right with 0

Right-then-left shift rounds down to the nearest multiple of 2^b!

49

0000 0001 0110 # 22

0000 0000 0010 # 2. >> 3 right-shift 3

0000 0001 0000 # 16. << 3 then left-shift 3

4795043

Quantization and Domain Size

Quantizing to fewer bits means fewer distinct values.

• akin to assigning "bin" numbers

​

Take 12-bit numbers and quantize down to the leading 4 bits.

How many distinct values do you expect?

​

​

50

🤔

A. 1

B. 4

C. 16

D. 256

E. 4096

F. Something else

4795043

Do not edit

How to change the design

Take 12-bit numbers and quantize down to the leading 4 bits. How many distinct values do you expect?

Presenting with animations, GIFs or speaker notes? Enable our Chrome extension

Quantization and Domain Size

Quantizing to fewer bits means fewer distinct values.

• akin to assigning "bin" numbers

​

Take 12-bit numbers and quantize down to the leading 4 bits.

How many distinct values do you expect?

​

​

52

🤔

A. 1

B. 4

C. 16

D. 256

E. 4096

F. Something else

4795043

Hierarchical Granularity

Scalar Functions

Aggregate Functions

Window Functions

Granularity Transformations

Numerical Granularity

Hierarchical Granularity

Rollup/Drilldown constructs

Implementing Explicit Hierarchies

53

Lecture 12, Data 101, Spring 2025

Data granularity implies multiple hierarchies

In this context, a hierarchy dictates the transformation into smaller/larger granularities.

Three categories of hierarchies, roughly:

• Explicit hierarchy, e.g., via a hierarchy table
• Time hierarchy, e.g., by second, by minute, hourly, daily, …
• Space hierarchy, e.g., geolocation of a physical phenomenon

54

4795043

Explicit Hierarchies

Many hierarchical models of the world.

• Domain, Kingdom, Phylum, Class, Order, Family, Genus, Species.
• City, County, State, Nation
• {Cars, Trucks, Planes} → Vehicles

55

4795043

Everything exists in Space-Time

Time

All data (not just physical phenomena) follow a bitemporal model for data:

• transaction time: the time a datum is recorded
• valid time: the time range when the datum is considered to be true

Space

All physical phenomena have a geolocation, which can be encoded in many ways:

• (latitude, longitude)
• place-name
• postal code

​

The most common dimensions of data!

56

4795043

Everything exists in Space-Time

Time

All data (not just physical phenomena) follow a bitemporal model for data.

Hierarchies are common:

• msec < sec < min < hour < day < month < year < …
• Timezones, etc. complicate hierarchies!

​

Many time subdivisions are periodic: seasons, months, etc.

Space

All physical phenomena have a geolocation, which can be encoded in many ways:

Explicit and often complex hierarchies…

• …because of geopolitics!
• U.S. congressional districts may span parts of counties
• Not all countries are recognized by the United Nations
• Often not strict hierarchies; have overlaps.

While we generally use general-purpose databases and tools, you may need special systems.

57

GIS: Geographic Information Systems

• Postgres Example: PostGIS [link]

Temporal databases

• Postgres Example: Timescale [link]