Questions tagged [partition-by]
266 questions
2
votes
2 answers
Apache spark: Write JSON DataFrame partitionBy nested columns
I have this kind of JSON data:
{
"data": [
{
"id": "4619623",
"team": "452144",
"created_on": "2018-10-09 02:55:51",
"links": {
"edit": "https://some_page",
"publish": "https://some_publish",
…
Peter Kažmirský
- 107
- 1
- 8
2
votes
1 answer
SQL Running total group with sum threshold reset
I have the table :
PersonID FirstName PersonAge
1 Pras 2
2 Deep 3
3 Test 4
4 Prash 2
5 ser 1
6 df 8
7 ddf 5
8 …
Ace McCloud
- 798
- 9
- 27
2
votes
3 answers
Optimizing SUM OVER PARTITION BY for several hierarchical groups
I have a table like below:
Region Country Manufacturer Brand Period Spend
R1 C1 M1 B1 2016 5
R1 C1 M1 B1 2017 10
R1 C1 M1 B1 …
user1330974
- 2,500
- 5
- 32
- 60
2
votes
1 answer
sql row_number() vs select row_number() to get data
i have the following table
Prod_id Units sold
1, 100
2, 95
3, 84
4, 95
5, 100
I want to know why the first query gives proper row counts while the 2nd one returns only 1's
select
(ROW_NUMBER() OVER…
heyNow
- 866
- 2
- 19
- 42
2
votes
2 answers
Query and Partition By clause group by window
I've the following code
declare @test table (id int, [Status] int, [Date] date)
insert into @test (Id,[Status],[Date]) VALUES
(1,1,'2018-01-01'),
(2,1,'2018-01-01'),
(1,1,'2017-11-01'),
(1,2,'2017-10-01'),
(1,1,'2017-09-01'),
…
Mariano G
- 255
- 4
- 14
2
votes
3 answers
Rank() over Partition by in mysql
I'm completely stumped as to create a new column "LoginRank" from rank() over(partition by x, order by y desc) in mysql.
From sql server i would write the following query, to create a column "Loginrank" that is grouped by "login" and ordered by…
Samuel Ellett
- 37
- 1
- 1
- 4
2
votes
2 answers
SQL Server DENSE_RANK()
I have a table where each line item contains a unit#, date stamp, and bed count. A record is created each day for each unit with the number of beds.
Unit DateTime Beds
----------------------
ICU 2011-03-23 12
ICU 2011-03-24 24
ICU …
mckeyes
- 23
- 2
2
votes
1 answer
How to apply partition by and row_number() on inner join and case statement in sql query
I have a sql query
select b.logtime,
b.beam_current,
b.beam_energy,
case when a.st2_vs2_bag1_onoff=0 then c.st2_vs2_bag1_rb ELSE 0 END
as st2_vs2_bag1_rb,
CASE when a.st2_vs2_bag8_onoff=0 then…
tiddi rastogi
- 526
- 3
- 10
- 33
2
votes
1 answer
SQL max() over(partition) returning multiple results
I have a simple query that I'm using to try and understand the SQL max() over() functionality as follows:
select *, max(mt.version) over (partition by mt.version)
from myTable mt
where mt.id = 'some uuid';
Edit: This concept is new to me so I'm not…
Michael Hogenson
- 1,292
- 1
- 15
- 31
1
vote
2 answers
Return all the results for the latest date
I have a table with the following columns:
city_id = city ID
city_name = city name
shape = polygon -> geography field
date = date when the shape was created
and populated with the following sample…
Fabien
- 87
- 8
1
vote
1 answer
Snowflake Grouping by rows of 25 based on a column
i have a query that will create a calculated priority (c_priority) by store based on a primary priority column, the query works well to calculate c_priority using row_number() over (partition by store), however i need to go one step further and also…
0ldSch00lGm3r
- 23
- 4
1
vote
2 answers
Create columns taking successive values from another column
date
message
rnk_id
2022-12-19 10:48:51
mess1
8
2022-12-19 10:57:13
mess2
8
2022-12-19 10:57:23
mess3
8
2022-12-19 10:57:49
mess4
8
2022-12-19 10:57:58
mess5
8
2022-12-19 10:58:07
mess6
8
2022-12-19 11:00:36
mess7
8
2023-02-06…
leerickx
- 84
- 10
1
vote
1 answer
Calculate the cumulative total between 2 columns until a non-zero value is reached in 1st column. Once non-zero value is reached, the sum restarts
I am querying a database using TSQL in SSMS.I have a dataset that contains two unique ID's, A and B. For each of these ID's I want to sum columns OAC and Adj cumulatively until the next non-zero value is reached in column OAC. In other words, the…
Hevant Bhojaram
- 27
- 6
1
vote
1 answer
from array to range by step in SQL Server
The array of numbers should be transferred to ranges to compress the number of records.
This is an…
Frits Nagtegaal
- 61
- 1
- 6
1
vote
1 answer
An efficient way to backward fill group values by type
I have a logs table with id, name, type, and date columns. And I want to create a new column which represents next id value from the 2nd type. Query must be grouped by name and only type1 values filled based on next type2 value.
logs…
maso
- 21
- 5