All Questions

Filter by
Sorted by
Tagged with
0
votes
0answers
18 views

How to implement row count for REST API?

I’m building a REST API backed by PostgreSQL. I'm encountering something I don’t think is very niche, but I’m having trouble finding any discussion of it. Good REST API practice dictates that if a ...
0
votes
3answers
76 views

Window Function to get pairs of records and compute the time difference

I'm working with an event dataset as the one in the example below: Event_Type Event_Timestamp Is_Active A 2010-10-01 00:00:00 1 B 2010-10-01 00:00:01 1 A ...
3
votes
1answer
30 views

Conditional Select from Window Function

I'm using lead() to calculate the difference in values between two records in the same table. This works fine until I need to not report on one of the records based on some other value in the window. ...
1
vote
1answer
71 views

using lag() on multiple columns (gaps and islands issue)

I have a problem I am trying to solve where I need to eliminate duplicate consecutive rows in a table using the first 7 columns as the basis to determine if it is a duplicate. The data looks like: ...
0
votes
0answers
25 views

Dividend reinvestment into shares query

I'm gonna get straight to the problem: Image dividend reinvestment as this scenario: You buy 10 shares of AAPL on 2018-01-01 for $10 -- AAPL pays $1 / per share dividend on 2018-02-01 -> you get $10 ...
0
votes
1answer
19 views

How can I write a window function using something like row_number to increment only on discrete values?

Say I want to do something like: select my_date, row_number() over(partition by my_date order by my_date asc) from my_table I want the row numbers to come out as: 2018-10-01,1 2018-...
2
votes
2answers
61 views

How to select blocks with sequential data and aggregate the ids

I have the following table: id(int) startDate(timestamp) endDate(timestamp) plan_id(int) planned(bool) machine(int) --------------------------------------------------------------...
0
votes
0answers
35 views

Postgresql view that calculate if payment is paid based on previous rows

I have go DB structure like here: db-fiddle I'm looking for some solution that in View for charge_line_tab table will add information if charge line is paid or not, based on previous records in ...
0
votes
1answer
895 views

Window function IGNORE NULLS workaround for PostgreSQL

With the following query I can use the LAG() function to repeat the last non null value of c column: SELECT coalesce(open_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000) ...
1
vote
2answers
95 views

Strip longest common suffix and concat in Postgres

Given a table t: id | name ------------ 1 | abcfug 1 | deffug 1 | hijfug 2 | etc How can I do something like: select string_agg(strip_lcs(name), ', ') from t where id = 1 returning: abc, def, ...
1
vote
0answers
32 views

How do you aggregate the delta of values between two arbitrary datetime columns?

Please look at the columns and types of a table called History: - id PRIMARY KEY - account_id STRING - total_spent FLOAT - date_created DATETIME Sample data for this table: (id, account_id, ...
0
votes
1answer
154 views

How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL

I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows: 42, 1 ...
0
votes
1answer
375 views

Counting using window function with partition over an empty table

If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that? select count(*) over (partition by field_name) from ...
1
vote
1answer
19 views

Finding average of multiple delta times

I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example: SELECT *, "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER ...
4
votes
1answer
462 views

Select last non-null value for given partition - Postgres 10

I have a table with the following structure: date city cntry free occ 2018-08-13 16:30:00 2 12 5 2018-08-13 16:30:00 2 12 0 2018-08-13 14:30:00 2 12 1 ...
0
votes
1answer
60 views

Add running count of specific state changes

I have a table in PostgreSQL with below fields, where timestamp is simplified as integers: ts status 1 m 2 m 3 i 4 s 5 s 6 i 7 i 8 m 9 s 10 m I want to break ...
1
vote
3answers
64 views

Show rows with non-unique value in one field

I'm familiar with how to aggregate rows as shown in this answer: Show only duplicate values I'm also familiar with how to filter the aggregated results using a HAVING clause. What I can't seem to ...
1
vote
1answer
47 views

Window function with order by returns strange results

Here is example: drop table if exists tst; create table tst ( num integer not null ); insert into tst values (1), (2), (3); -- window functions WITH order by clause select *, max(num) over (...
2
votes
2answers
492 views

Filter on time difference between current and next row

I have a database table with the following structure: id | created -------+--------------- 1 | 2018-07-23 00:01:00 2 | 2018-07-23 00:02:00 ... Now I'd like to return all rows where the ...
2
votes
1answer
37 views

How to count rows based on a column value

I am using Posgresql and I want to write a query to perform the following. Suppose the table is like this: id section views 1 1 10 2 2 8 3 1 20 4 1 21 5 2 ...
5
votes
1answer
475 views

Grouping data based on cumulative sum

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using ...
1
vote
1answer
61 views

How to force Redshift/Postgres to aggregate nth_value?

I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this: select customer_email, nth_value(created_at, 1) over (partition by ...
2
votes
1answer
46 views

SELECTing min, max (maybe length) of continuous id ranges in a table

I have a table foo in Postgres (10.3) # select * from foo; id | cust | load_dt ----+-------+------------ 1 | cust1 | 2 | cust1 | 3 | cust1 | 4 | cust2 | 2018-05-15 5 | cust2 | 2018-05-15 ...
1
vote
2answers
77 views

How can I assign group IDs depending on content?

I have the following table with data as shown below; linenrs are monotonically increasing but not necessarily consecutive; when the key field contains an ellipsis ... that indicates an entry that is ...
2
votes
1answer
4k views

Calculate Running Sum of Each Row From Start, Even When Filtering Records

I have this simplified table entries: ---------------------------- | id | date | amount | ---------------------------- | 1 | 2017-12-01 | 100 | ---------------------------- | 2 | 2017-12-...
0
votes
1answer
37 views

Form groups computing max - min depending on number of peers

This is table: CREATE table point ( datetime timestamp, value float) I have data in some range of time 7:00 - 7:15: 0 5 : 5 7:15 - 7:30: - - : null 7:30 - 7:45: 7 - :...
2
votes
3answers
28 views

Summary over sets of points

Using postgresql 10 I have a table gpspoints which among other things contains id dist 1 NULL 2 2.3 3 4.4 4 1.7 5 1.2 6 2.7 and so on Dist is distance in meter ...
4
votes
1answer
758 views

Carry over long sequence of missing values with Postgres

I have a table like this: create table foo (foo_label text, foo_price int, foo_date date); insert into foo ( values ('aaa', 100, '2017-01-01'), ('aaa', NULL, '2017-02-...
2
votes
2answers
466 views

dense_rank max OVER 3 days?

How do I get the MAX (or another aggregate function) for of a column of each row, OVER its 3 days worth of rows? SQL example with expected output and db schema: http://sqlfiddle.com/#!17/24686/3 ...
0
votes
1answer
60 views

How to pivot a table skipping bad value in Postgres

Given a table like: label | x | y | z -------+-------+-------+------ AAA | NULL | 7 | 9 BBB | 0 | 10 | 0 CCC | 12 | NULL | 0 create table coords (label ...
2
votes
4answers
2k views

Set non-negative floor for rolling sum, in PostgreSQL

This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data, CREATE TABLE foo AS ...
3
votes
3answers
95 views

How to set a value if certain columns are the same across multiple rows

I am doing a data migration and I end up with a temporary table as follows: curid cuid rtid cd dd rm 10 4 4 2016-01-02 2016-07-02 16 4 4 2016-06-12 2016-12-12 ...
2
votes
2answers
1k views

Window function to merge rows that meet a condition

I have the following schema DROP TABLE IF EXISTS messages; DROP TABLE IF EXISTS chats; CREATE TABLE chats ( client integer NOT NULL, provider integer NOT NULL, PRIMARY KEY (client, provider) );...
2
votes
2answers
2k views

Nested aggregate functions over different dimensions

I have a table where I periodically record the throughput of all network interfaces on a set of servers: create table net ( ts timestamptz not null, host text not null, interface text not ...
2
votes
1answer
81 views

PostGIS window function not picking up index?

I am executing this query ST_ClusterDBSCAN with PostGIS extension and PostgreSQL. I have indexed point_data but an EXPLAIN ANALYZE shows me that the index is not being picked up. How can I rearrange ...
1
vote
1answer
435 views

Window aggregate resulting in slow query on postgres 9.6

I'm using Postgresql 9.6 and I'm running into a performance issue. I have a relatively simple query: explain analyze SELECT * FROM ( SELECT A.person_id, O.session_id, timestamp_ AS ...
2
votes
2answers
555 views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
2
votes
1answer
5k views

Solving “Gaps and Islands” with row_number() and dense_rank()?

How does one solve the islands part of gaps-and-islands with dense_rank() and row_number(). I've seen this now a few times and I'm wondering if someone could explain it, Let's use something like this ...
1
vote
1answer
578 views

Is WITH RECURSIVE the right tool for my task?

Table setup: create table users(id bigserial, group_id bigint); insert into users(group_id) values (1), (1), (2), (1), (3), (3); Query: WITH RECURSIVE r AS ( SELECT users.id, users....
1
vote
1answer
65 views

Do I need ORDER BY in a window, if I already have a properly ordered query?

In this query.. SELECT * FROM foo ORDER BY x; Does a window function over foo need a specific ORDER BY clause or does the window share the ordering of the query? Question inspired by @Erwin, and @...
13
votes
7answers
1k views

Grouping or Window

I have a situation I think can be solved using window function but I'm not sure. Imagine the following table CREATE TABLE tmp ( date timestamp, id_type integer ) ; INSERT INTO tmp ...
0
votes
1answer
192 views

Why can't you do basic operations AFTER an aggregate in a window function? Like (max(x)+5), vs (5+max(x))

The syntax for aggregates and window functions is confusing, this does not work, SELECT (max(x)+5) OVER () FROM generate_series(1,10) AS t(x); While this works SELECT 5+max(x) OVER () FROM ...
1
vote
1answer
176 views

Data discretization based on rank position

Lets say I have a table with the schema: id value 1 0.3 2 0.6 5 0.1 4 0.7 Provided by CREATE TABLE foo AS SELECT * FROM ( VALUES (1,0.3::float),(2,0.6),(5,0.1),(4,0.7) ) AS x(id, ...
5
votes
1answer
342 views

Calculate inventory out row unit price

Currently I have data like this, CREATE TABLE foo (id, qty, unit_price, date, action) AS VALUES (1, 2000, 4.01235, '2015-10-10'::timestamp, 'in'), (2, -30, NULL , '2015-10-11'::...
8
votes
2answers
6k views

Using window function to carry forward first non-null value in a partition

Consider a table that records visits create table visits ( person varchar(10), ts timestamp, somevalue varchar(10) ) Consider this example data (timestamp simplified as counter) ts| person ...
6
votes
1answer
2k views

Row by row subtraction with single input number

I want to subrtact dynamically from previous row subtraction result and as input i want to give a single number. I have table a CREATE TABLE a (id int, code text, qty numeric); And data in it ...
1
vote
3answers
83 views

How to add a summary count to this query

I am using Postgres 9.4 and I have a query that looks like the following: SELECT attendees.event_id, attendees.user_id, users.tags FROM users, attendees WHERE 'crystal:promo' = ANY(users.tags) AND ...
1
vote
1answer
224 views

Postgresql 9.4 incrementing value issue

I am trying to go from the first table: test4 (pid, event, time) and count how many events transition to another (in second table eventtransitions). For instance, in the final table, there should be ...
3
votes
1answer
899 views

Row number with reset in PostgreSQL

I have this data set in PostgreSQL: User_id | date | is_cleared | ---------------+-------------------------+------------+ C | 2016-08-03 18:28:38.37 | ...
1
vote
1answer
58 views

Transformation of row data based on a grouping of rows preceding it

The basis for this question is: Transformation of row data based on preceding row for special cumulative sum which I had asked recently. I will now raise the level of complexity a bit. First, I will ...