SQL Window Functions Examples
The window function examples use a view named q1_sales that was created from a CSV file named emp_sales and stored in a directory on the local file system.
The emp_sales.csv file contains the following information:
|----------------|----------------|-----------|-------|
| emp_name | emp_mgr | dealer_id | sales |
|----------------|----------------|-----------|-------|
| Beverly Lang | Mike Palomino | 2 | 16233 |
| Kameko French | Mike Palomino | 2 | 16233 |
| Ursa George | Rich Hernandez | 3 | 15427 |
| Ferris Brown | Dan Brodi | 1 | 19745 |
| Noel Meyer | Kari Phelps | 1 | 19745 |
| Abel Kim | Rich Hernandez | 3 | 12369 |
| Raphael Hull | Kari Phelps | 1 | 8227 |
| Jack Salazar | Kari Phelps | 1 | 9710 |
| May Stout | Rich Hernandez | 3 | 9308 |
| Haviva Montoya | Mike Palomino | 2 | 9308 |
|----------------|----------------|-----------|-------| You can create a CSV file named emp_sales with this data.
Drill was installed locally and a workspace was created in the dfs storage plugin configuration for the directory where the emp_sales.csv file is located. See Installing Drill.
If you create a CSV file with the data provided, you can create workspace that points to the directory where you store the emp_sales.csv file. See Configuring Storage Plugins.
When you run show schemas, Drill lists the workspace that you configured as a schema. In the following example, you can see dfs.emp listed. This is the workspace that points to the directory where the emp_sales.csv file is stored.
0: jdbc:drill:zk=local> show schemas;
|--------------------|
| SCHEMA_NAME |
|--------------------|
| INFORMATION_SCHEMA |
| cp.default |
| dfs.default |
| dfs.emp |
| dfs.root |
| dfs.tmp |
| sys |
|--------------------|
You can then run the USE command to change to the schema with the file. All queries are executed against the schema that you use.
0: jdbc:drill:zk=local> use dfs.emp;
|------|-------------------------------------|
| ok | summary |
|------|-------------------------------------|
| true | Default schema changed to [dfs.emp] |
|------|-------------------------------------|
To create the q1_sales view used in the examples, issue the following query with the CREATE VIEW command.
Note: You must use column numbers when querying CSV files. Also, CAST the columns to a specific data type to avoid incorrect implicit casting by Drill. This can affect the accuracy of window function results. In Drill, the column array starts with 0 as the first column.
0: jdbc:drill:zk=local> create view q1_sales as select cast(columns[0] as varchar(30)) as emp_name, cast(columns[1] as varchar(30)) as emp_mgr, cast(columns[2] as int) as dealer_id, cast(columns[3] as int) as sales from `q1_sales.csv`;
|------|----------------------------------------------------------|
| ok | summary |
|------|----------------------------------------------------------|
| true | View 'q1_sales' created successfully in 'dfs.emp' schema |
|------|----------------------------------------------------------|
1 row selected (0.134 seconds)
Query the view to verify that all of the data appears correctly:
select * from q1_sales;
|----------------|----------------|-----------|-------|
| emp_name | emp_mgr | dealer_id | sales |
|----------------|----------------|-----------|-------|
| Beverly Lang | Mike Palomino | 2 | 16233 |
| Kameko French | Mike Palomino | 2 | 16233 |
| Ursa George | Rich Hernandez | 3 | 15427 |
| Ferris Brown | Dan Brodi | 1 | 19745 |
| Noel Meyer | Kari Phelps | 1 | 19745 |
| Abel Kim | Rich Hernandez | 3 | 12369 |
| Raphael Hull | Kari Phelps | 1 | 8227 |
| Jack Salazar | Kari Phelps | 1 | 9710 |
| May Stout | Rich Hernandez | 3 | 9308 |
| Haviva Montoya | Mike Palomino | 2 | 9308 |
|----------------|----------------|-----------|-------|
10 rows selected (0.112 seconds)
Now, you can run the window function example queries on your machine.