Grouping Sets
SELECT SUM(quantity) as qty
FROM inventory;
SELECT product, SUM(quantity) as qty
FROM inventory
GROUP BY product;
SELECT warehouse, product, SUM(quantity) AS qty
FROM inventory
GROUP BY warehouse, product;
UNION ALL
SELECT warehouse, null, SUM(quantity) as qty
FROM inventory
GROUP BY warehouse
UNION ALL
SELECT null, product, SUM(quantity) as qty
FROM inventory
GROUP BY product
UNION ALL
SELECT null, null, SUM(quantity) as qty
FROM inventory;
SELECT COALESCE(warehouse, 'ALL') as store,
COALESCE(product, 'ALL') as product,
SUM(quantity) qty
FROM INVENTORY
GROUP BY
GROUPING SETS(
(warehouse, product),
(warehouse),
(product),
()
)
ORDER BY qty desc;
SELECT COALESCE(warehouse, 'All Stores'),
COALESCE(product,'All Phones'),
SUM(quantity) as qty
FROM inventory
GROUP BY ROLLUP (warehouse, product);
SELECT RANDOM();
—- Or
SELECT RAND()
FROM dual;
SELECT RANDOM()*(b-a)+a;
SELECT RANDOM()*(25-10)+10;
SELECT FLOOR(RANDOM()*(b-a+1))+a;
SELECT FLOOR(RANDOM()*(25-10+1))+10;
SELECT SETSEED(.123);
SELECT RANDOM();
OR
SELECT SETSEED(.123), RANDOM(), ran RANDOM dom();
In ORACLE OR SQL SERVER:
SELECT RAND(.123)...;
SELECT rownum rn, DBMS_RANDOM.RANDOM
FROM all_objects
WHERE 100000 >= rownum;
WITH
RandomNumbers (RowNumber, RandomNumber) AS (
SELECT 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
UNION ALL
-- Recursive member definition
SELECT rn.RowNumber + 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
FROM RandomNumbers rn
WHERE rn.RowNumber < 10
)
SELECT rn.RowNumber,
rn.RandomNumber
FROM RandomNumbers rn;
SELECT *
FROM GENERATE_SERIES(1,100);
SELECT *
FROM GENERATE_SERIES(2,100);
SELECT *
FROM GENERATE_SERIES(1,100,4);
SELECT TO_CHAR(day, 'YYYY-MM-DD'),
RANDOM()
FROM GENERATE_SERIES (
'2019-02-01'::date,
'2020-04-01'::date,
'1 day'::interval
) day;
SELECT TO_CHAR(day, 'YYYY-MM-DD'),
RANDOM() * 7 + 10
FROM GENERATE_SERIES (
'2019-02-01'::date,
'2020-04-01'::date,
'1 day'::interval
) day;
consSELECT
TO_CHAR(day, 'YYYY-MM-DD'),
RANDOM() * 7 + 10 * (ROW_NUMBER() OVER()) AS value
FROM GENERATE_SERIES (
'2019-02-01'::DATE,
'2020-04-01'::DATE,
'1 day'::INTERVAL
) day;ole.log( 'Code is Poetry' );
SELECT day,
RANDOM() * 7 + 10 * (ROW_NUMBER() OVER()) AS value
FROM (
SELECT TO_CHAR(gen_days, 'YYYY-MM-DD') AS day
FROM GENERATE_SERIES (
'2019-02-01'::DATE,
'2020-04-01'::DATE,
'1 day'::INTERVAL
) gen_days
ORDER BY day DESC
) dat
ORDER BY day ASC;
SELECT
TO_CHAR(DAY, 'YYYY-MM-DD'),
RANDOM() * 7 + 10 * (1.1^(ROW_NUMBER() OVER())) AS value
FROM GENERATE_SERIES (
'2019-02-01'::DATE,
'2020-04-01'::DATE,
'1 day'::INTERVAL
) day;
SELECT *,
-LN((RANDOM()+.01)) * 5 AS x
FROM GENERATE_SERIES(1, 2000);
SELECT
TO_CHAR(DAY, 'YYYY-MM-DD'),
RANDOM() *
7 + 10 * (1000 + 1000 * RANDOM())*(.9^(ROW_NUMBER()
OVER())) AS value
FROM GENERATE_SERIES (
'2019-02-01'::DATE,
'2020-04-01'::DATE,
'1 day'::INTERVAL
) day;
SELECT
TO_CHAR(DAY, 'YYYY-MM-DD'),
RANDOM() * 7 + 10 * (1000 + 500 *
RANDOM()) * LOG(ROW_NUMBER()
OVER()) AS value
FROM GENERATE_SERIES (
'2019-02-01'::DATE,
'2020-04-01'::DATE,
'1 day'::INTERVAL
) day;
SELECT *
FROM company_ages;
SELECT MAX(IQR) AS IQR
FROM (
SELECT upper_quartile_break - LAG(upper_quartile_break) OVER () AS IQR
FROM (
SELECT
age_quartile,
MAX(age) AS upper_quartile_break
FROM(
SELECT
age,
NTILE(4) OVER (ORDER BY age) AS age_quartile
FROM company_ages) AS quartiles
WHERE age_quartile IN (1, 3)
GROUP BY age_quartile
) diff
) iqr
SELECT c, COUNT(r) / 1000000.0 AS x
FROM GENERATE_SERIES(0,49) AS c
LEFT OUTER JOIN RANDOM_EXP(1000000, 5) AS r
ON c = FLOOR(r)
GROUP BY c
ORDER BY c;
SELECT *
FROM edges;
SELECT *
FROM graph;
SELECT
COUNT(*) AS count,
AVG(n) AS mean,
STDDEV(n) AS std_deviation
FROM (
SELECT * FROM (
SELECT
2 * RANDOM() - 1 AS x,
2 * RANDOM() - 1 AS y
FROM GENERATE_SERIES(0, 100)
) gs
LEFT JOIN LATERAL (
SELECT gs.x*gs.x + gs.y*gs.y AS s
WHERE gs.x*gs.x + gs.y*gs.y < 1
) s ON TRUE
LEFT JOIN LATERAL (
SELECT
gs.x * SQRT((-2 * LN(s.s))/s.s) AS n
UNION
SELECT
gs.y * SQRT((-2 * LN(s.s))/s.s) AS n
) u ON TRUE
) random_dist_check
WHERE n IS NOT NULL;
CREATE TABLE
normal_nums_with_outliers AS (
SELECT FLOOR(normal_rand(200, 38, 3)) AS num
UNION ALL
-- lower outliers
SELECT FLOOR(random() * 5 + 23) AS num
FROM GENERATE_SERIES(1,6)
UNION ALL
-- upper outliers
SELECT FLOOR(random() * 9 + 62) AS num
FROM GENERATE_SERIES(1,4)
);
SELECT TO_CHAR(day, 'YYYY-MM-DD'),
random() * 7 + 10 as value
FROM GENERATE_SERIES (
'2021-02-01'::date,
'2022-04-01'::date,
'1 day'::interval
) day;
SELECT TO_CHAR(DAY, 'YYYY-MM-DD'),
RANDOM() * 7 + 10 * (1000 + 1000 *
RANDOM())*(.9^(ROW_NUMBER()
OVER()
)
) AS value
FROM GENERATE_SERIES (
'2019-02-01'::DATE,
'2020-04-01'::DATE,
'1 day'::INTERVAL
) day;
SELECT *,
-LN((RANDOM()+.01)) * 5 AS x
FROM GENERATE_SERIES(1, 20)
ORDER by x;