Stack Overflow
Stack Overflow

Questions tagged [partition-by]

266 questions
1
vote
0 answers

MySQL window function (last_value) does not work - syntax error

I try to run the query below but a syntax error occurs. I checked many times how to write a last value window function correctly but still there must be a mistake that I cannot see. I tried to add three different kind of frame_clause but none of…
Bendegúz Tunyogi
  • 33
  • 3
1
vote
3 answers

partition by - how to properly use in order to fetch related row

:) I use postgresql 13 and I was sure I understand how to use PARTITION BY but I guess I was wrong. I created the following example to illustrate my problem. I created this table: id, group_id,order_id,text 1 1 1 a1 2 1 2 …
ufk
  • 30,912
  • 70
  • 235
  • 386
1
vote
1 answer

How to order partitioned column to the beginning in Spark DataFrame?

I am having data which looks like this , 1,IN,abc 2,US,pqr 3,UK,rst 4,IN,xyz 5,US,lmn I am creating a dataframe over this data using spark-2.4.5 (scala) val df =…
Sandeep Patil
  • 21
  • 4
1
vote
2 answers

Oracle SQL LAG function return the wrong calculation in Time

My calculation at first row column Different_Time is incorrect data after second row is correct data until row 10 is incorrect data again then next row the calculation is back to correct calculation again with using LAG function.Appreciate if…
Yong
  • 31
  • 8
1
vote
2 answers

SQL Server - Pivot Convert rows to columns (with additional row data)

Can someone help me with this SQL? I am looking to pivot multiple rows into columns by: id, type, color, date Challenge: I have multiple columns I want to preserve/be shown on the pivoted table. id | type | color | date | country_code |…
Jacky Lam
  • 67
  • 1
  • 2
  • 7
1
vote
1 answer

Find the row value from which minimum value was extracted over window.partitionBy in PySpark

I have a PySpark dataframe like this: +--------+-------------+--------------+-----------------------+ |material|purchase_date|mkt_prc_usd_lb|min_mkt_prc_over_1month| +--------+-------------+--------------+-----------------------+ | Copper| …
borderline_coder
  • 183
  • 1
  • 13
1
vote
3 answers

SQL Cumulative sum in a partition by

Consider a table with customers, sorted dates and amounts as follows User Date Purchase Joe '2020-01-01' 10 Joe '2020-02-01' 20 Joe '2020-02-20' 20 Joe '2020-03-15' 15 Lucy '2020-01-12' 5 Lucy '2020-02-15' 30 Lucy '2020-02-21' 20 Lucy…
simon
  • 615
  • 4
  • 13
1
vote
1 answer

ROW_NUMBER() OVER (PARTITION BY giving Syntax error in mysql5.7

I am using partition by to get duplicate rows and this query returning syntax error in mysql5.7 select column1,ROW_NUMBER() OVER (PARTITION BY column2, column3 ORDER BY column2 DESC) as RowNumber from tableA Error: ERROR 1064 (42000): You have…
Vikram Singh Shekhawat
  • 714
  • 1
  • 10
  • 27
1
vote
2 answers

pyspark: get the last observation in each subgroup

I want to get the last observation at the end of each minute for each stock. My high-frequency dataframe looks like: +-----+--------+-------+----------+----------+----------+ |stock| date | hour | minute | second | price …
FlyUFalcon
  • 314
  • 1
  • 4
  • 18
1
vote
2 answers

How do you Window.partitionBy over a range or condition?

I have some data that is ordered by dateTime, and I want to add up the values based on another column(1 or 0). HOWEVER, I need it done such that it only sums the values up to 5 seconds after. How do I do this? ex Table |ID |GPS_TimeStamp …
Ranald Fong
  • 401
  • 3
  • 12
1
vote
1 answer

.rowsBetween(Window.unboundedPreceding, Window.unboundedFollowing) Error Spark Scala

Hello I am trying to extend the last value of each window to the rest of the window for the column count in order to create a flag which recognizes if the register is the last value of a window. I tried it this way but did not work. Sample DF: val…
Javier de la Iglesia
  • 125
  • 1
  • 10
1
vote
1 answer

SQL Except Operator and with CTE to select all the items in 1st query but not in 2nd Query

I am trying to get all the data in the 1st query below but except the data from the 2nd query below. Here 1st I am trying to select the unique data/distinct data by using with cte and partition by. I tried using except, but I get this…
Punith GP
  • 690
  • 4
  • 11
  • 33
1
vote
1 answer

Query for generating month level & YTD level data

Write SQL Queries to - Given - Day level Sales Data for different Companies 1) Create Month Level , YTD Level Data for the given data. YTD- Year to Date YTD(Mar) = Jan + Feb +Mar 2) Create Overall level on the basis of Company for the data…
chahak joshi
  • 23
  • 5
1
vote
1 answer

ORA-00923: Use over (partition by) with Xmlagg in Oracle

I have a query of the form below and I need it to partition by a certain column but when I place it like this, it gives me the error : from keyword not found where expected Select distinct t_name, rtrim(xmlagg(xml element(e, text,…
Highdef
  • 73
  • 8
1
vote
4 answers

Dplyr equivalent of SUM over PARTITION BY

I'm sure this question has been asked before, but I can't find the answer. Here's my data: df <- data.frame(group=c("a","a","a","b","b","c"), value=c(1,2,3,4,5,7)) df #> group value #> 1 a 1 #> 2 a 2 #> 3 a 3 #> 4 b …
Rez99
  • 359
  • 1
  • 4
  • 15
1 2 3
…
17 18