在同一财政年度从最远的月份取回值
原标题：retrieve value from month furthest back in same FY
I have a dataset where rows correspond to data collected at different points in time like so:
metric date FY result
visits 03/01/2022 FY23 17
visits 04/01/2022 FY23 21
visits 05/01/2022 FY23 34
visits 06/01/2022 FY24 99
visits 07/01/2022 FY24 11
visits 08/01/2022 FY24 27
I want to add a column that shows the value from the result column that (a) is in the same FY as the current month AND (b) is furthest back -every metric in the dataset set does not have values 12 months back, some may have 3 or 7 months or 11 months, etc....
desired output:
metric date FY result year_ago
visits 03/01/2022 FY23 17 17
visits 04/01/2022 FY23 21 17
visits 05/01/2022 FY23 34 17
visits 06/01/2022 FY24 99 99
visits 07/01/2022 FY24 11 99
visits 08/01/2022 FY24 27 99
I am trying to use LAG in a case statement to loop through the previous 12 months but it returns 0 instead, not sure if multiple expressions are matching or mismatching strings and integers?
CASE
WHEN result <> 0 AND LAG(FY,1) = FY THEN COALESCE(LAG(result,1) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,2) = FY THEN COALESCE(LAG(result,2) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,3) = FY THEN COALESCE(LAG(result,3) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,4) = FY THEN COALESCE(LAG(result,4) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,5) = FY THEN COALESCE(LAG(result,5) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,6) = FY THEN COALESCE(LAG(result,6) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,7) = FY THEN COALESCE(LAG(result,7) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,8) = FY THEN COALESCE(LAG(result,8) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,9) = FY THEN COALESCE(LAG(result,9) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,10) = FY THEN COALESCE(LAG(result,10) OVER (PARTITION BY date ORDER BY date),0)
WHEN result <> 0 AND LAG(FY,11) = FY THEN COALESCE(LAG(result,11) OVER (PARTITION BY date ORDER BY date),0)
ELSE 0
END AS year_ago
问题回答
Your requirement is easy to come by using window functions along with conditional aggregation:
WITH cte AS (
SELECT t.*, MIN(date) OVER (PARTITION BY FY) AS min_date
FROM yourTable t
)
SELECT metric, date, FY, result,
MAX(CASE WHEN date = min_date THEN result END) OVER (PARTITION BY FY) year_ago
FROM cte
ORDER BY FY, date;
相关问题
SQL SubQuery getting particular column
I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question:
The subquery below returns a Table with 3 ...
难以执行 REGEXP_SUBSTR
I m 查询Oracle 10g。 我有两张表格(样本数据见下文)。 i m 试图提取一些领域
SQL Query for Performing Radius Search based on Latitude Longitude
We have a restaurant table that has lat-long data for each row.
We need to write a query that performs a search to find all restaurants within the provided radius e.g. 1 mile, 5 miles etc.
We have ...
SQL Query Shortcuts
What are some cool SQL shorthands that you know of?
For example, something I learned today is you can specify to group by an index:
SELECT col1, col2 FROM table GROUP BY 2
This will group by col2
PHP array callback functions for cleaning output
I have an array of output from a database. I am wondering what the cleanest way to filter the values is
example array
Array
(
[0] => Array
(
[title] => title 1
...
OracleParameter and DBNull.Value
we have a table in an Oracle Database which contains a column with the type Char(3 Byte).
Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn t work:
OracleCommand ...
Running numbers in SQL
I have a SQL-statement like this:
SELECT name FROM users WHERE deleted = 0;
How can i create a result set with a running number in the first row? So the result would look like this:
1 Name_1
2 ...
How to get SQL queries for each user where env is production
I’m developing an application dedicated to generate statistical reports, I would like that user after saving their stat report they save sql queries too. To do that I wrote the following module:
...
