Questions tagged [partition-by]
266 questions
0
votes
1 answer
Oracle query ordered by balanced distribution
It's hard to explain by words, so i'll try with an example:
Name
Age
Sex
Liam
20
M
William
21
M
Emma
21
F
Oliver
22
M
Sophia
22
F
Isabella
23
F
Mia
23
F
Olivia
24
F
James
24
M
I have to try to balance the distribution of…
Lelehaine
- 13
- 2
0
votes
2 answers
Pivot two DateTime rows into a pair of columns when one part of the pair may be missing
I am working on a time clock application for my company that is capturing clock-in/clock-out data in a table named TCPunch (TimeClock Punch). ActionID 1 = ClockIn, ActionID 2= ClockOut. If either row for a ClockIn/ClockOut pair is missing, I would…
David Christian
- 75
- 7
0
votes
1 answer
Cognos Lag + over partition
I am trying to recreate this in cognos:
lag(queue_name,1) respect nulls over (partition by appl_num order by seq_nmbr)
The logic works fine in terradata but cannot find a way to make this work in cognos.
Full query below for further…
kyleisapenguin
- 15
- 2
0
votes
1 answer
SQL Error Msg 207, Level 16, State 1, Line 59 Invalid column name 'dstbadge'
I'm trying to use ROW_NUMBER() function to give me distinct groups of my data but keep getting an Invalid column name error. Here is my query:
SELECT
ROW_NUMBER() OVER (PARTITION BY Access.Number ORDER BY Access.Time) dstbadge,
Access.Event…
0
votes
0 answers
MySQL - partition table by key field and select latest row ordered by event column as view
I am running MySQL Community Server v5.7.40 and have 2 tables: "customers" and "events" created as follows:
create table customers (customer_id int, name varchar(100), primary key (customer_id));
create table events (event_id int, customer_id int,…
nick_j_white
- 534
- 6
- 27
0
votes
1 answer
How to query only 1 ID out of multiple rows and choose based on the maximum date with amazon redshift?
I have this data structure in my table. One row represents 1 date and 1 status
id date status
1 2022-02-12 in
1 2022-02-13 out
1 2022-02-14 dlv
2 2022-02-13 in
2 2022-02-14 …
nomnom3214
- 227
- 1
- 11
0
votes
1 answer
How to remove the 0s in the id_sum column by a sequence from 1 to n in pyspark dataframe
I have the following pyspark dataframe df_model:
id_client
id_sku
1111
4444
1111
4444
2222
6666
2222
6666
3333
777
And i use this code to generate the column id_frecuence:
t = df_model.collect()[0][0]
w =…
Fernando_V_S
- 3
- 2
0
votes
0 answers
Getting around the over (partition by) clause in mySQL
I have been trying to run this query in order to get a rolling count of people vaccinated in different locations but have been receiving a syntax error and I am not sure what to do
Here's the query;
SELECT
dea.continent,
dea.location,
…
Pamela
- 7
- 1
0
votes
0 answers
SUMIFS Function in SQL - SUM() OVER (PARTITION BY ) is not working
I used the SUMIFS formula in my google sheet to sum the product cost based on Order Id and Composite Parent SKU if it is equal to SKU. However, I cannot get the result I want using SUM OVER PARTITION BY in SQL.
Here is my…
Irene
- 1
- 1
0
votes
0 answers
Pandas equivalent of partition by
I have the following dataframe:
df = pd.DataFrame({'group':['A','A','A','B','B','B'],'value':[1,2,3,4,5,6]})
I would like to add a column that will sum all the values for each group. This way each row with group A will have 6 in that column and…
Hana
- 49
- 5
0
votes
0 answers
How to translate PARTITION BY with a FIRST_VALUE() in linq?
It should resolve with a group by, in all rows of the result must load the first value, that will repeat in each group
I tried with a group by
0
votes
1 answer
Sum over Partition By Only when Value is Greater than 0
I only want to sum the applied amount when a ledger amount in another table is positive
Example
Table A
Statement # ID
500 1
500 2
500 3
500 4
Table…
0
votes
0 answers
How to use partition by method of pyspark to split a pyspark dataframe into different csvs based on multiple columns
I have a pyspark dataframe (data). I need to separate the df by multiple columns and save them as csv to particular folders . The folder names will be based on the column name after partition.
PATH = '/../' + data['Col1'] + data[Col2] + data[Col3] +…
Akshata
- 111
- 5
0
votes
0 answers
LAG Function in Windows Functions
I have this query to know the status of customers. To know if the customers was active or not active
I have got this error in BIGQUERY :" Window framing clause is not allowed for analytic function lag" but i don't know who i can make this…
Nobbies_data
- 31
- 1
- 5
0
votes
2 answers
get previous non-NULL value based on LAG function with dynamic offset
I have a tricky situation.
I have a source dataset; it has data for four employees and their departments based on an effective date.
I need to convert this source dataset to the destination dataset.
Both datasets are properly sorted by EmployeeName…
user3812887
- 439
- 12
- 33