PRIMER_VALOR()
Syntax: FIRST_VALUE ( expression ) OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )
Analicemos la sintaxis anterior:
- expresión
- La cláusula PARTITION BY se usa para dividir filas en un conjunto de resultados.
- ORDENAR POR
- cláusula_rango_filas
Primero, cree dos tablas Mammals Animal_groups
CREATE TABLE Animal_groups ( animal_id serial PRIMARY KEY, animal_name VARCHAR (255) NOT NULL ); CREATE TABLE Mammals ( mammal_id serial PRIMARY KEY, mammal_name VARCHAR (255) NOT NULL, lifespan DECIMAL (11, 2), animal_id INT NOT NULL, FOREIGN KEY (animal_id) REFERENCES Animal_groups (animal_id) );
Ahora inserte algunos datos en la tabla:
INSERT INTO Animal_groups (animal_name) VALUES ('Terrestrial'), ('Aquatic'), ('Winged'); INSERT INTO Mammals(mammal_name, animal_id, lifespan) VALUES ('Cow', 1, 10), ('Dog', 1, 7), ('Ox', 1, 13), ('Wolf', 1, 11), ('Blue Whale', 2, 80), ('Dolphin', 2, 5), ('Sea Horse', 2, 3), ('Octopus', 2, 8), ('Bat', 3, 4), ('Flying Squirrels', 3, 1), ('Petaurus', 3, 2);
PRIMER_VALOR()
SELECT mammal_id, mammal_name, mammal_id, lifespan, FIRST_VALUE(mammal_name) OVER( ORDER BY lifespan ) lowest_lifespan FROM Mammals;
Producción:
Ejemplo 2:
PRIMER_VALOR()
SELECT mammal_id, mammal_name, mammal_id, lifespan, FIRST_VALUE(mammal_name) OVER( PARTITION BY animal_id ORDER BY lifespan RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) lowest_lifespan FROM Mammals;
Producción:
Publicación traducida automáticamente
Artículo escrito por RajuKumar19 y traducido por Barcelona Geeks. The original can be accessed here. Licence: CCBY-SA