4,622 questions
1
vote
3
answers
292
views
Performing cumulative sum in a Window with different columns ordering and null last configuration in Polars
Polars version: 1.25.2
I have a dataframe:
from datetime import date
test_df = pl.DataFrame([
("A", None, date(2009, 1, 24), 1),
("A", date(2010, 3, 24), date(2013, 1, 24),...
- 2,295
2
votes
3
answers
132
views
Calculate difference between two values, including those only appearing once within the partition
DB<>Fiddle
CREATE TABLE inventory (
id SERIAL PRIMARY KEY,
stock_date DATE,
product VARCHAR,
stock_balance INT
);
INSERT INTO inventory
(stock_date, product, stock_balance)VALUES ...
- 5,565
3
votes
3
answers
161
views
How to retrieve a sub-array from result of array_agg?
I have a SQL table in postgres 14 that looks something like this:
f_key
data1
data2
fit
1
{'a1', 'a2'}
null
3
1
{'b1', 'b2'}
{'b3'}
2
2
{'c1', 'c2'}
null
3
Note that data1 and data2 are arrays.
I need ...
- 303
1
vote
2
answers
181
views
How to efficiently calculate an exponential moving average in postgres?
I'm trying to calculate the average true range on some time series dataset stored in postgres. Its calculation requires a 14 period exponential moving average of true range which based on the answer ...
- 61
5
votes
4
answers
249
views
Sort aggregated query results by two methods simultaneously
I need to sort a query's results by two methods at the same time.
I want the first 3 records (returned) to be based on their prevalence in another table
And then I want the rest of the results sorted ...
- 453
-1
votes
2
answers
193
views
Calculate SUM over a primary key and between dates
My query:
SELECT
c.CustID,
o.OrderID,
SUM(ol.Qty * ol.Price) AS SUMOrder,
AVG(SUM(ol.Qty * ol.Price)) OVER (PARTITION BY c.CustID) AS AVGAllOrders,
COUNT(*) AS Countorders,
SUM(...
- 41
-1
votes
1
answer
173
views
Assign unique values in a set-based approach
Simplifying, I have the following data:
Col1
Col2
A
X
A
Y
A
Z
B
X
B
Y
B
Z
C
Z
I need to receive the following result:
Col1
Col2
A
X
B
Y
C
Z
In other words: For each value in the left column, I need to ...
- 11
0
votes
0
answers
69
views
Polars bug using windowed aggregate functions on Decimal type columns
Windowed aggregate functions on Decimal-types move decimals to integers
I found a bug in polars (version 1.21.0 in a Python 3.10.8 environment) using windowed aggregate functions. They are not ...
- 935
3
votes
1
answer
118
views
Why `.first()`, and why before `.over()`, in `with_columns` expression function composition chain
new to Polars, seeking help understanding why part of the function composition for the expression in the .with_columns() snippet below has to be done in that particular order.
Specifically, I don't ...
- 31
2
votes
2
answers
73
views
Compute group-wise residual for polars data frame
I am in a situation where I have a data frame with X and X values as well as two groups GROUP1 and GROUP2. Looping over both of the groups, I want to fit a linear model against the X and Y data and ...
- 1,351
0
votes
1
answer
55
views
BigQuery get rolling average of variable 1 if variable 2 >= quantile
Say I want to get the rolling average of variable x where a second variable y is in the top 5th percentile (over that window).
I can get the rolling average alone with something like this
SELECT
...
- 567
1
vote
1
answer
67
views
How to calculate the maximum drawdown of a stock over a rolling time window?
In quantitative finance, maximum drawdown is a key risk metric that measures the largest decline from a peak to a trough over a period.
I want to calculate the maximum drawdown over the past 10 ...
2
votes
1
answer
212
views
Find corresponding date of max value in a rolling window of each partition
Sample code:
import polars as pl
from datetime import date
from random import randint
df = pl.DataFrame({
"category": [cat for cat in ["A", "B"] for _ in range(1, ...
- 2,295
1
vote
1
answer
155
views
Get a grouped sum in polars, but keep all individual rows
I am breaking my head over this probably pretty simply question and I just can't find the answer anywhere. I want to create a new column with a grouped sum of another column, but I want to keep all ...
- 647
1
vote
1
answer
62
views
Group-By column in polars DataFrame inside with_columns
I have the following dataframe:
import polars as pl
df = pl.DataFrame({
'ID': [1, 1, 5, 5, 7, 7, 7],
'YEAR': [2025, 2025, 2023, 2024, 2020, 2021, 2021]
})
shape: (7, 2)
┌─────┬──────┐
│ ID ┆ ...
- 3,611
