4,621 questions
-2
votes
1
answer
111
views
How to display in postgresql only the records that are not within the 10 minutes [closed]
For example, I have this table below, how can I display only the records that aren't in the 10 minutes range. In this example should return the first and last in note examples
user_id | ...
- 2,711
3
votes
3
answers
132
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
4
votes
4
answers
228
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 ...
- 443
-1
votes
2
answers
192
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
0
votes
0
answers
68
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
2
votes
2
answers
70
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
1
vote
1
answer
48
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 ...
1
vote
1
answer
141
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 ...
- 637
1
vote
1
answer
78
views
In PostgreSQL do ranking window functions heed the window frame or act on the entire partition?
I am learning window functions, primarily with this page of the docs. I am trying to categorize the window functions according to whether they heed window frames, or ignore them and act on the ...
1
vote
1
answer
72
views
Median with a sliding window
The goal is to use MEDIAN as a window function with a sliding window of a specific size.
SELECT *,
MEDIAN(n) OVER(ORDER BY id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM test_data
ORDER BY id;...
- 181k
2
votes
2
answers
73
views
Identify duplicates within a period of time using Redshift SQL
In a table, I have plan details of customers with their customer_id and enroll_date.
Now, I want to identify duplicate and valid enrollments from the overall data.
Duplicate: If a customer enrolls a ...
1
vote
1
answer
62
views
MySQL filtered gaps and islands: avoiding temporaries and filesorts?
CREATE TABLE `messages` (
`ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`Arrival` TIMESTAMP NOT NULL,
`SenderID` INT UNSIGNED NOT NULL,
-- Fields describing messages skipped
PRIMARY ...
1
vote
1
answer
85
views
Aggregate 3-month rolling dates with overlap [closed]
Suppose I have below dataset:
date Value
01-Jul-24 37
01-Aug-24 76
01-Sep-24 25
01-Oct-24 85
01-Nov-24 27
01-Dec-24 28
And I want to aggregate by 3 months rolling:...
- 13
1
vote
2
answers
168
views
How can I perform a calculation on a rolling window over a partition in polars?
I have a Dataset containing GPS Coordinates of a few planes. I would like to calculate the bearing of each plane at every point in time.
The Dataset as among others these columns:
event_uid
plane_no
...
- 385
4
votes
1
answer
110
views
Grouped Rolling Mean in Polars
Similar question is asked here
However it didn't seem to work in my case.
I have a dataframe with 3 columns, date, groups, prob. What I want is to create a 3 day rolling mean of the prob column values ...
- 327
1
vote
2
answers
106
views
SQL Window Functions - Pivot on a Column
I have a table data as show below.
cust_id
city_type
city_name
start_date
1
physical
Las Vegas
5/17/2024
1
office
Seattle
5/17/2024
1
office
Dallas
9/20/2024
1
physical
Dallas
10/30/2024
1
office
...
- 13
0
votes
3
answers
121
views
Oracle Max Over Partition By Excluding Current Row
I have an issue to calculate the max() value over partition by where i want to exclude the current row each time.
Assuming I have a table with ID, Group and Valbue.
calculating max/min/etc. over ...
- 45
8
votes
1
answer
415
views
Replacing window function OVER() with WINDOW clause reference yields different results
While preparing an answer to another question here, I coded up a query that contained multiple window functions having the same OVER(...) clause. Results were as expected.
select ...
sum(sum(s....
- 10.6k
1
vote
1
answer
101
views
Update every N rows with an increment of 1
I am an SQL server developer working on a project in a PostgreSQL environment. I am having some PostgreSQL syntax issues. I am working off version 9.3.
In a given table, I am trying to set every 10 ...
- 1,943
0
votes
0
answers
16
views
Is there a good way to add columns calculated using a window partition within pandas chaining
My background is in SQL and I was wondering what was the most efficient/readable way of creating multiple columns using the same window partition within a pandas chain.
Suppose I have the following ...
- 23
0
votes
1
answer
64
views
spark scala ignore nulls in windowing clause
In spark SQl, you can write
SELECT title, rn,
lead(rn, 1) IGNORE NULLS over(order by rn) as next_rn
FROM my_table
;
How would you add the IGNORE NULLS part in the equivalent Scala code?
val ...
- 81
0
votes
3
answers
200
views
Using SQL Server window functions with year and month(Period of time)
Please consider this script:
Declare @tbl Table
(
F1 int,
F2 int,
Year int,
Month tinyint
)
Insert into @tbl
values
(10, 1, 2020, 1),
(10, 1, 2020, 2),
(10, 1, 2020, 3),
(10, ...
- 13.1k
1
vote
1
answer
104
views
How to assign unique UUIDs to groups of metrics in a PostgreSQL table with repeated names?
I’m working with a PostgreSQL table that stores metric data for different assets. The table currently has over 1 billion records.
Each update will have multiple metrics, e.g., speed, distance, ...
- 7,579
1
vote
1
answer
75
views
Sum a Column of a Timeseries by an Order Number when the Ordernumber is not unique
I have a table like this: demo at db<>fiddle
CREATE TABLE test(id, order_id, start, end1, count) AS VALUES
(1, 1, '2023-12-19 10:00:00'::timestamp, '2023-12-19 11:00:00'::timestamp, 15),
(2, 1, '...
- 31
1
vote
4
answers
117
views
Number of missing periods between dates
I'm using Postgres and I would like to find missing ranges of dates. I've got this table with these data :
create table event_dates(date)AS VALUES('2024-12-09'::date)
...
- 13