我该如何为此编写SQL查询？我需要一个SQL查询 [英] How can I write a SQL query for this? I need a SQL query for this

问题描述

标题国家市场日期



标题A澳大利亚54 01-02-18

标题A澳大利亚54 02-04-18

Title A Australia 54 29-09-18

Title A Australia 54 17-04-19

Title A Australia 54 15-04-20

Title A Australia 54 20-04-20

Title A Australia 54 10-05-22

Title A Australia 54 09-05- 23

Title B Australia 54 29-09-18

Title B Germany 54 07-01-19

让我们考虑三个日期a， b，c和三个日期属于同一标题，国家和市场。现在我的要求是，如果两个连续日期之间的差异小于365天，那么周期应为1，否则它应该增加1到下一个数字。如果周期递增，则应将参考日期更改为当前日期，并且应在下一个日期和更改的参考日期之间测量差异，并且该过程将持续到当前组，即日期属于同一标题，国家和市场处理。



然后这个过程从下一组开始再次开始。



输出应该如下：标题国家市场日期周期



标题A澳大利亚54 01-02-18 1

标题A澳大利亚54 02-04- 18 1

Title A Australia 54 29-09-18 1

Title A Australia 54 17-04-19 2

Title A Australia 54 15-04-20 2

Title A Australia 54 20-04-20 3

Title A Australia 54 10-05-22 4

澳大利亚54 09-05-23 4

Title B Australia 54 29-09-18 1

Title B Germany 54 07-01-19 1



我尝试了什么：



尝试使用case语句并尝试使用多个case和decode语句

解决方案

请参阅此处： ORACLE-BASE - LAG和LEAD分析函数 [ ^ ]

title country market date

Title A Australia 54 01-02-18
Title A Australia 54 02-04-18
Title A Australia 54 29-09-18
Title A Australia 54 17-04-19
Title A Australia 54 15-04-20
Title A Australia 54 20-04-20
Title A Australia 54 10-05-22
Title A Australia 54 09-05-23
Title B Australia 54 29-09-18
Title B Germany 54 07-01-19
Let us consider three dates a,b,c and the three dates belongs to same title, country and market. Now my requirement is if the difference between two consecutive dates is less than 365 days then cycle should 1 else it should be incremented by one to next number. If the cycle is incremented then the reference date should be changed to the current date and the difference should be measured between next date and changed reference date and the process continues on till the current group i.e., dates belongs to same title, country and market are processed .

Then the process begins again from the beginning for the next set.

the output should as below: title country market date cycle

Title A Australia 54 01-02-18 1
Title A Australia 54 02-04-18 1
Title A Australia 54 29-09-18 1
Title A Australia 54 17-04-19 2
Title A Australia 54 15-04-20 2
Title A Australia 54 20-04-20 3
Title A Australia 54 10-05-22 4
Title A Australia 54 09-05-23 4
Title B Australia 54 29-09-18 1
Title B Germany 54 07-01-19 1

What I have tried:

tried with case statements and tried with multiple case and decode statements

解决方案

See here: ORACLE-BASE - LAG and LEAD Analytic Functions[^]

这篇关于我该如何为此编写SQL查询？我需要一个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！