英文:
Is there a way to partition by incremental series in Postgressql?
问题
在PostgreSQL中，您可以使用PARTITION BY和窗口函数来实现以下结果：
SELECT
last_name,
year,
increment,
SUM(CASE WHEN increment < 0 THEN 1 ELSE 0 END) OVER (PARTITION BY last_name ORDER BY year) AS partition
FROM your_table_name;
这将根据last_name进行分区，并在year列的顺序下计算increment列小于0的行数，以获得partition列的值。
英文:
In postgressql is there a way to attain the result below by using partition by or any other way?
last_name year increment partition
Doe 2000 1 1
Doe 2001 2 1
Doe 2002 3 1
Doe 2003 -1 2
Doe 2004 1 3
Doe 2005 2 3
Doe 2006 3 3
Doe 2007 -1 4
Doe 2008 -2 4
答案1
得分: 1
SELECT last_name,
year,
increment,
SUM(CASE WHEN increment < 0 THEN 1 ELSE 0 END) OVER (PARTITION BY last_name ORDER BY year) AS partition
FROM your_table
ORDER BY last_name, year;
英文:
SELECT last_name,
year,
increment,
SUM(CASE WHEN increment < 0 THEN 1 ELSE 0 END) OVER (PARTITION BY last_name ORDER BY year) AS partition
FROM your_table
ORDER BY last_name, year;
答案2
得分: 1
看起来你想将连续的正/负值分组在一起，一种方法是使用两个row_number函数之间的差异，这将对分区进行处理，但分组号码是无序的。
select *,
row_number() over (partition by last_name order by year) -
row_number() over (partition by last_name,
case when increment>=0 then 1 else 2 end order by year) as prt
from tbl
order by last_name, year
如果你想要按顺序分区（1、2、3...），你可以尝试另一种方法，使用lag和累加和，如下所示：
select last_name, year, increment,
1 + sum(case when sign(increment) <> sign(pre_inc) then 1 else 0 end) over
(partition by last_name order by year) as prt
from
(
select *,
lag(increment, 1 , increment) over
(partition by last_name order by year) pre_inc
from tbl
) t
order by last_name, year
[查看示例][1]
[1]: https://dbfiddle.uk/1z3qeV4o
英文:
It seems that you want to group the consecutive positive/ negative values together, one option is to use a difference between two row_number functions, this will make the partition but with unordered group numbers.
select *,
row_number() over (partition by last_name order by year) -
row_number() over (partition by last_name,
case when increment>=0 then 1 else 2 end order by year) as prt
from tbl
order by last_name, year
If you want the partitions in order (1, 2, 3...) you could try another approach using lag and running sum as the following:
select last_name, year, increment,
1 + sum(case when sign(increment) <> sign(pre_inc) then 1 else 0 end) over
(partition by last_name order by year) as prt
from
(
select *,
lag(increment, 1 , increment) over
(partition by last_name order by year) pre_inc
from tbl
) t
order by last_name, year
答案3
得分: 1
以下是翻译后的内容：
如果递增列在年份列上增加，它将被标记为1；否则，将标记为0。然后，我们使用“LAG”来对连续的数据进行分组，无论递增是正数还是负数。
with cte as (
select * ,
row_number() over (partition by last_name order by year) as row_num,
case when increment >= LAG(increment,1,0) over (partition by last_name order by year)
then 1 else 0 end rank_num
from mytable
),
cte2 as (
select *, LAG(rank_num,1,1) over (partition by last_name order by year) as pre
from cte
order by year
)
select last_name, year, increment, 1+sum(case when pre <> rank_num then 1 else 0 end) over
(partition by last_name order by year) as partition
from cte2;
英文:
If the increment column does encrease over the column year, it will be marked as 1; otherwise, it will be marked as 0. Then, we group the successive data using "LAG", regardless of whether the increment is positive or negative.
with cte as (
select * ,
row_number() over (partition by last_name order by year) as row_num,
case when increment >= LAG(increment,1,0) over (partition by last_name order by year)
then 1 else 0 end rank_num
from mytable
),
cte2 as (
select *, LAG(rank_num,1,1) over (partition by last_name order by year) as pre
from cte
order by year
)
select last_name, year, increment, 1+sum(case when pre <> rank_num then 1 else 0 end) over
(partition by last_name order by year) as partition
from cte2;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论