I would like to get output the way its shown in the image attached and the way the input is organized is also shown in the image attached. Thanks!
Tag: gaps-and-islands
Group Date ranges with separate groups if other date in between
I have a Table like this: I want to select an Start Date and an End date and group them by Specification. So my Output should look like this: I tried it with: but this doesnt give my desired result. Does anyone have an Idea? Answer You can try the below – it’s a gap & island problem
How to unnest the table based on date interval in Presto?
I have a table with events, where start_dt is a start and end_dt an end of the event. This table is partitioned by dt column derived from the end_dt. This means that events that start before and end after midnight are present only in one partition. What I need to do is to split each event into as many rows
How to get a continuous group of id
I’d like to increment an id by group, for each variation of an attribute with only 2 values. like this : result wanted I tried dense_rank() with window function but i only gives me 1 or 2 in grp column Answer You can use window functions to solve this gaps and islands problem. Her is an approch using lag() and
SQL Getting row number only when the value is different from all previous values
I want the count adding one only when the value has not been show before. The base table is: The goal is: I am thinking to compare the current row with all previous rows, but I have difficulty to call all previous rows. Thank you! Answer Several different ways to accomplish this. I guess you’ll get to pick one you
SQL query grouping by range
Hi have a table A with the following data: I’d like to have the following result: My DB is DB2/400. I have tried with ROW_NUMBER partitioning, subqueries but I can’t figure out how to solve this. Answer I understand this as a gaps-and-island problem, where you want to group together adjacent rows that have the same PA. Here is an
How to group data if difference between rows is mothe than 1 minute
Im trying to create a new table on SQL which will have summarized data. For example. In the original table i have this data: If you see rows 9, 10 and 11 they are consecutive, so i want to group them …
Rank based on cumulative value
I want to rank on ID and value columns based on ascending order of UID. Expected output has to change once value column has a different value than the previous value. Ranks has to restart on each new ID Here is a sample dataset that I have created: Answer I think that the simplest approach to this gaps-and-islands problem is
SQL – get summary of differences vs previous month
I have a table similar to this one: It contains the stores that are active at BOM (beginning of month). How do I query it to get the amount of stores that are new that month – those that where not active the previous month? The output should be this: I now how to count the first time that each
Find the longest rainless time (every hour, beyond several days)
I have a weather station where I measure the amount of rain per hour and save the data in a database. The rain-table looks like this: Now I want the longest rainless time per month. I already have a solution, but it only works for the respective day. However, if there is no rain for several days, my solution will