pivot by

Example 1

sym = `C`MS`MS`MS`IBM`IBM`C`C`C
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:35:42,09:36:51,09:36:59,09:35:47,09:36:26,09:34:16,09:35:26,09:36:12]
t2 = table(timestamp, sym, qty, price);

t2;

select price from t2 pivot by timestamp, sym;

a=select last(price) from t2 pivot by timestamp.minute(), sym;

a;

typestr a;
// output
TABLE

b=exec count(price) from t2 pivot by timestamp.minute(), sym;
b;

typestr b;
// output
FAST DOUBLE MATRIX

Example 2. Calculate the basket value of an ETF's constituents. For simplicity, assume an ETF has 2 constituents AAPL and FB.

symbol=take(`AAPL, 6) join take(`FB, 5)
time=2019.02.27T09:45:01.000000000+[146, 278, 412, 445, 496, 789, 212, 556, 598, 712, 989]
price=173.27 173.26 173.24 173.25 173.26 173.27 161.51 161.50 161.49 161.50 161.51
quotes=table(symbol, time, price)
weights=dict(`AAPL`FB, 0.6 0.4)
ETF = select symbol, time, price*weights[symbol] as price from quotes;

select price from ETF pivot by time, symbol;

To calculate the basket value of this ETF's constituents at each timestamp that any of its constituents had a trade, we need to forward fill each stock's price in the pivot table, and then sum each row's weighted price. They can be completed in just one SQL statement with "pivot by".

select rowSum(ffill(price)) from ETF pivot by time, symbol;

Example 3. The names of the newly created columns from a pivot by clause will be automatically adjusted.

Before version 2.00.2, column names in DolphinDB can only use letters, digits or underscores (_), and must start with letters.

Since version 2.00.2, column names generated by pivot by, addColumn can contain special characters or start with digits.

For details, please refer to section Creating tables in Table.

date = take(2021.08.01  2021.08.02 2021.08.03, 12)
sym = take(["IBM N", "_MSFTN", "3_GOOGS", ""], 12).sort()
value = 1..12
t=table(date, sym, value)
re = select value from t pivot by date, sym

select  _"NULL" from re

select  _"3_GOOGS" from re

In the following example, set removeSpecialCharInColumnName = true. The stock symbols "600300" and "600600" are adjusted to "c600300" and "c600600" as column names.

symbol=take(`600300, 6) join take(`600600, 5)
time=2019.02.27T09:45:01.000000000+[146, 278, 412, 445, 496, 789, 212, 556, 598, 712, 989]
price=173.27 173.26 173.24 173.25 173.26 173.27 161.51 161.50 161.49 161.50 161.51
quotes=table(symbol, time, price)
weights=dict(`600300`600600, 0.6 0.4)
ETF = select symbol, time, price*weights[symbol] as price from quotes;

select last(price) from ETF pivot by time, symbol;

Example 4. The pivot by clause includes more than 2 columns.

date = 2020.09.21 + 0 0 0 0 1 1 1 1
sym = `MS`MS`GS`GS`MS`MS`GS`GS$SYMBOL
factorNum = 1 2 1 2 1 2 1 2
factorValue = 1.2 -3.4 -2.5 6.3 1.1 -3.2 -2.1 5.6
t = table(date, sym, factorNum, factorValue);

t;

select factorValue from t pivot by date, sym, factorNum;

Select multiple columns for each factorNum

select factorValue, factorValue>0 as factorSign from t pivot by date, sym, factorNum;

Example 5. Retain duplicate values after applying function asis

In this example, the following conditions are met:

• table pt is a DFS table

• The from clause does not contain table joins.

• The first n-1 pivot-by column (i.e., the time column) does not contain DECIMAL128 values.

• The last pivot-by column (i.e., the sym column) is a partitioning column.

• The selected column value does not contain NULL values. Aggregate functions, order-sensitive functions, row-based functions, or fill functions are not used in the select clause.

• The table generated by the pivot by clause can be fully populated. In this example, for each distinct value in the time column, the number of values in the last pivot-by column sym is always equal. When time=10:20:44, the sym A, B, and C all have one record; When time=10:20:45, the sym A, B, and C all have one record; When time=10:20:46, the sym A, B, and C all have two records.

Apply function asis to the value column, all data can be retained:

time = [10:20:44,10:20:44,10:20:44,10:20:45,10:20:45,10:20:45,10:20:46,10:20:46,10:20:46,10:20:46,10:20:46,10:20:46]
sym = ["A","B","C","A","B","C","A","B","C","A","B","C"]
value = [510,434,999,837,402,615,495,885,745,968,975,165]
t = table(time,sym,value)

db = database("dfs://test_pivot",VALUE,`A`B`C)
pt = db.createPartitionedTable(t,`pt,`sym)
pt.append!(t)

select value from pt pivot by time,sym

select asis(value) from pt pivot by time,sym

Example 6. Use pivot by with select or exec clause to query and aggregate array vector columns.

Create an in-memory table where the column val1 contains array vectors:

id = `A`B`B`B`C`C`D`D`D
val1 = array(DOUBLE[], 0, 10).append!([1.0, 2.1 2.2 2.4, 2.1 2.2 2.4, 3, 5, 1.6 7, 8.9 100, 2.3, 1.1 4 5.1 4]);
val2 = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:35:42,09:36:51,09:36:59,09:35:47,09:36:26,09:34:16,09:35:26,09:36:12]
t = table(timestamp, id, val1, val2);

// Query the val1 value for each id every minute
select val1 from t pivot by timestamp.minute(), id;

Apply aggregation on val1 with select and return a table:

select max(val1) from t pivot by timestamp.minute(), id;

Apply aggregation on val1 with exec and return a matrix:

exec max(val1) from t pivot by timestamp.minute(), id;