How to Use FIRST_VALUE and LAST_VALUE Functions in Snowflake
0 بار بازدید -
2 ماه پیش
-
Learn how to use the
Learn how to use the FIRST_VALUE and LAST_VALUE analytical functions in Snowflake. This tutorial provides step-by-step instructions and examples to help you master these powerful functions for your data analysis needs
------------
--First_value & Last_value
-- The FIRST_VALUE function returns the first value in an ordered set of values.
-- The LAST_VALUE function returns the last value in an ordered set of values.
--Syntax
/*
FIRST_VALUE |LAST_VALUE (expr) [ { RESPECT | IGNORE } NULLS ]
OVER (analytic_clause)
*/
-- Create the sales table
CREATE OR REPLACE TABLE sales (
sale_id INT,
sale_date DATE,
category STRING,
amount DECIMAL(10, 2)
);
-- Insert sample data into the sales table
INSERT INTO sales (sale_id, sale_date, category, amount) VALUES
(1, '2024-01-01', 'Electronics', 100.00),
(2, '2024-01-02', 'Electronics', 150.00),
(3, '2024-01-03', 'Electronics', 200.00),
(4, '2024-01-04', 'Clothing', 50.00),
(5, '2024-01-05', 'Clothing', 75.00),
(6, '2024-01-06', 'Clothing', 60.00),
(7, '2024-01-07', 'Electronics', 120.00),
(8, '2024-01-08', 'Electronics', 80.00),
(9, '2024-01-09', 'Electronics', 90.00),
(10, '2024-01-10', 'Clothing', 100.00),
(11, '2024-01-11', 'Clothing', 110.00),
(12, '2024-01-12', 'Electronics', 140.00),
(13, '2024-01-13', 'Electronics', 160.00),
(14, '2024-01-14', 'Clothing', 130.00),
(15, '2024-01-15', 'Clothing', 140.00),
(16, '2024-01-16', 'Electronics', 170.00),
(17, '2024-01-17', 'Clothing', 120.00),
(18, '2024-01-18', 'Clothing', 150.00),
(19, '2024-01-19', 'Electronics', 130.00),
(20, '2024-01-20', 'Electronics', 110.00);
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT
FROM SALES;
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT,
first_value(AMOUNT) over ( )
FROM SALES;
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT,
last_value(AMOUNT) over ( )
FROM SALES;
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT,
FIRST_VALUE(AMOUNT) over ( ORDER BY AMOUNT),
last_VALUE(AMOUNT) over ( ORDER BY AMOUNT)
FROM SALES
ORDER BY AMOUNT;
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT,
MIN(AMOUNT) over ( )
FROM SALES;
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT,
FIRST_VALUE(AMOUNT) over ( ORDER BY AMOUNT range between unbounded PRECEDING and current row )
FROM SALES;
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT,
FIRST_VALUE(AMOUNT) over ( ORDER BY SALE_DATE)
FROM SALES;
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT,
FIRST_VALUE(AMOUNT) over (partition by category ORDER BY AMOUNT ),
LAST_VALUE(AMOUNT) over (partition by category ORDER BY AMOUNT )
FROM SALES;
SELECT
SALE_ID, SALE_DATE, CATEGORY, AMOUNT,
FIRST_VALUE(AMOUNT) over (partition by category ORDER BY AMOUNT ),
LAST_VALUE(AMOUNT) over (partition by category ORDER BY AMOUNT )
FROM SALES
order by category , AMOUNT;
--Understanding NULL VALUES
CREATE OR REPLACE TABLE sales_with_nulls (
id INT,
sales_date DATE,
sales_amount INT
);
INSERT INTO sales_with_nulls (id, sales_date, sales_amount) VALUES
(1, '2024-01-01', 100),
(2, '2024-01-02', NULL),
(3, '2024-01-03', 200),
(4, '2024-01-04', NULL),
(5, '2024-01-05', 300);
select * from sales_with_nulls
order by sales_amount;
select * from sales_with_nulls
order by sales_amount desc;
select sales_amount,first_value(sales_amount) over ( order by sales_amount desc )
from sales_with_nulls;
select sales_amount,first_value(sales_amount) IGNORE nulls over ( order by sales_amount desc )
from sales_with_nulls
order by sales_amount desc ;
select sales_amount,first_value(sales_amount) IGNORE nulls over ( order by sales_amount desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
from sales_with_nulls
order by sales_amount desc ;
select sales_amount, last_value(sales_amount) IGNORE nulls over ( order by sales_amount desc )
from sales_with_nulls
order by sales_amount desc ;
---------
#Snowflake #DataAnalytics #SQLTutorial #FIRSTVALUE #LASTVALUE #SnowflakeDatabase #DataAnalysis #SQLFunctions #TechTutorial #DataScience #BigData #Analytics #DatabaseTutorial #SQLQueries #DataEngineering
2 ماه پیش
در تاریخ 1403/04/27 منتشر شده
است.
0
بـار بازدید شده