Questions tagged [partition-by]
266 questions
1
vote
3 answers
partition by a count of a field
I have a table t1 with two int fields(id,month) and I have populated it with some values.
What I would like to see as an output is, the maximum of (count of id in a month). I have tried the following code and it works fine:
select id,max(freq) as…
Raghavi Kannan
- 67
- 1
- 7
1
vote
1 answer
incorrect syntax in over and partition by with temporary tables
I have a partition-by function in my stored procedure which calls for a temporary table from a previous partition-by function. The table that I created with the previous partition by function is #tempVehicleManifestRow which in turn is what I call…
marchemike
- 3,179
- 13
- 53
- 96
1
vote
1 answer
saving to a temporary table while having an over partition command in mssql
I want to save my select statement to a temporary table but it is using a partition command, which is why I do not know how it would be saved.
This is my sample select statement:
;WITH A
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY…
marchemike
- 3,179
- 13
- 53
- 96
1
vote
1 answer
Condition windowing function
Assume this query,
SELECT
SUM (Quantity) OVER (Partition By Symbol ORDER BY PositionID ASC ROWS UNBOUNDED PRECEDING) as RemainedQty
Is it possible to define conditions in the windowing function?
In this example I need to get SUM (Quantity)…
SJ.Jafari
- 1,236
- 8
- 27
- 39
0
votes
1 answer
How to update a column value with 1 when the table has duplicated records in Big query
I am trying to update a column in a table when they are duplicated rows found. Initially, I tried to partition by all columns but later found that FLOAT dtypes are not allowed.
update table tabale_name
set column_to_update =1
where exists(SELECT…
data en
- 431
- 1
- 2
- 9
0
votes
1 answer
Using Partition By to compare values based on date in Hive SQL
I have a table called referrals
CREATE TABLE [dataproduct].[referrals]
(
[person_id] DOUBLE,
[medical_service_cd] DOUBLE,
[refer_from_organization_id]
[referral_org_name] STRING,
[refer_from_provider_id] DOUBLE,
…
Nishad Gulvady
- 65
- 5
0
votes
1 answer
HIVE SQL - row_number() OVER (partition BY
Hi I'm trying to populate the row_number using row_number() over (partition, but the results is not giving me the row number 1 where the first record is cumulative_cost >= threshold and the subsequent rows all have the same row number.
This is the…
user15676
- 123
- 2
- 10
0
votes
0 answers
partition by start and end of a submission workflow
I have some data where a user submits change requests and an approver approves or sends back for more information. The table doesn't have any parent/child relationships set up so it can get a bit messy visually and hard to keep track of that…
0
votes
2 answers
repartition in memory vs file
repartition() creates partition in memory and is used as a read() operation. partitionBy() creates partition in disk and is used as a write operation.
How can we confirm there is multiple files in memory while using repartition()
If repartition…
Blue Clouds
- 7,295
- 4
- 71
- 112
0
votes
1 answer
Calculating the correlation of each row with one in Group in Python
I need some help to calculate 4 metrics for my table.
What is the correct way to constantly refer to the same string within the department?
I have been trying to solve this problem for a very long time, but I can not come to a logical solution.
I…
DNau
- 1
- 1
0
votes
1 answer
How to update the row based on a matched row from the data grouped by in Sparksql
From the table below, update the var_1 and var_2:
-- Group by id,nr
-- If prod for (type = 'sold' or page = 'bag') = prod for (type = 'gift')
--- update var_1 and var_2 from the max(pg_nr) of the matching row
My_Table
…
D.Aquar
- 33
- 8
0
votes
0 answers
Can't remove duplicates with Partition by
I am trying to remove duplicate employee records by creating a column that will number each row for an individual 'emp_name', then removing all but the first entry of each. I seem to be missing whatever is required that will allow me to use the new…
0
votes
2 answers
Teradata Count number of rows using a window function excluding a duplicate condition
I have the following data in my Teradata tables and I am trying to get the number of rows as 'TENURE' based on some conditions
Have
Id MONTH ORDERNO STATUS
101 2022-01-31 105 00
101 2022-02-28 …
ckp
- 579
- 2
- 10
- 27
0
votes
1 answer
Running Total formula in Netsuite Saved Search is duplicating each row
I am using the SUM OVER PARTITION formula in Netsuite transaction saved search to get the running total of item quantity.
SUM /* comment */ {quantity} OVER(PARTITION BY {item} ORDER BY {trandate} ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT…
BillyZ87
- 1
- 1
0
votes
1 answer
Partitioning by id in another table. Bigquery
I have users in that sometimes are registred twice with the same value for pn "Phonenumber". Knowing that pn PhoneNumber is the same I know that they should be the same rid "RegisterID". Trouble is that pn is not very reliable either and sometimes…
Herman Wilén
- 195
- 12