May 04

Oracle Advanced SQL Clauses

By Sayed in ব্লগ । Blog

Group By

group by attr1, attr2

group by ROLLUP(attr1, attr2)

group by CUBE(attr1, attr2)

Rank, Dense_RANK, ROW_number:

RANK() OVER (ORDER BY PRICE) as "PR",
ROW_NUMBER() OVER (ORDER BY PRICE) as "PR"
DENSE_RANK() OVER (ORDER BY C DESC NULLS LAST) as "R"
DENSE_RANK() OVER (PARTITION by C ORDER BY P) as "PR"
RANK() OVER (ORDER BY P) as "PR"
avg(C) OVER() AS "AC"
MIN(Y) KEEP (DENSE_RANK FIRST ORDER BY Y) as "FirstItem"
MIN(Y) KEEP (DENSE_RANK LAST ORDER BY Y) as "LASTItem"

Hierarchical Query:

START WITH employee_id = 102
CONNECT BY FOLLOWING m_id = e_id;

Keep First or Last Row

KEEP (DENSE_RANK FIRST ORDER BY …)
KEEP (DENSE_RANK LAST ORDER BY …)

PARTITION BY on RANK/Dense_Rank

RANK()
RANK() OVER
RANK() OVER PARTITION BY
DENSE RANK() OVER
DENSE RANK() OVER PARTITION BY
PARTITION BY …. ORDER BY
ROW_NUMBER() OVER (ORDER By …)
ROWS BETWEEN Unbounded Preceding and CURRENT ROW
RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND INTERVAL ‘5’ DAY Following
ROWS BETWENN 1 PRECEDING and 1 FOLLOWING
DENSE RANK() OVER (PARTITION BY …..)
DENSE RANK() OVER (PARTITION BY …..ORDER BY …)

May 04

Oracle Advanced SQL Clauses

By Sayed in ব্লগ । Blog

Group By

Apr 28

Lombak, Getter Setter Example

By Sayed in ব্লগ । Blog

Ref: https://projectlombok.org/features/GetterSetter

import lombok.AccessLevel; import lombok.Getter; import lombok.Setter;


public class GetterSetterExample {

/**

* Age of the person. Water is wet.

*

* @param age New value for this person's age. Sky is blue.

* @return The current value of this person's age. Circles are round.

*/

@Getter @Setter private int age = 10;
/**

* Name of the person.

* -- SETTER --

* Changes the name of this person.

*

* @param name The new value.

*/

@Setter(AccessLevel.PROTECTED) private String name;

@Override public String toString() { return String.format("%s (age: %d)", name, age); } }

"Project Lombok is a java library that automatically plugs into your editor and build tools, spicing up your java.
Never write another getter or equals method again, with one annotation your class has a fully featured builder, Automate your logging variables, and much more."