Skip to content

Instantly share code, notes, and snippets.

@onderkalaci

onderkalaci/ComplexCountDIstinct.sql

Created May 27, 2016 11:34
Show Gist options
  • Save onderkalaci/66f6f6ccefd6c695c8f9db7bb3095e26 to your computer and use it in GitHub Desktop.
Save onderkalaci/66f6f6ccefd6c695c8f9db7bb3095e26 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ComplexCountDIstinct.sql
CREATE TABLE lineitem (
l_orderkey bigint not null,
l_partkey integer not null,
l_suppkey integer not null,
l_linenumber integer not null,
l_quantity decimal(15, 2) not null,
l_extendedprice decimal(15, 2) not null,
l_discount decimal(15, 2) not null,
l_tax decimal(15, 2) not null,
l_returnflag char(1) not null,
l_linestatus char(1) not null,
l_shipdate date not null,
l_commitdate date not null,
l_receiptdate date not null,
l_shipinstruct char(25) not null,
l_shipmode char(10) not null,
l_comment varchar(44) not null,
PRIMARY KEY(l_orderkey, l_linenumber) );
SELECT master_create_distributed_table('lineitem', 'l_orderkey', 'append');
COPY lineitem FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/lineitem.1.data' with delimiter '|';
COPY lineitem FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/lineitem.1.data' with delimiter '|';
COPY lineitem FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/lineitem.2.data' with delimiter '|';
COPY lineitem FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/lineitem.2.data' with delimiter '|';
CREATE TABLE lineitem_local AS SELECT * FROM lineitem;
-- SUM (DISTINCT col)
-- Check correctness of regression tests on regular table
-- COUNT (DISTINCT round_to_100(col))
-- COUNT (DISTINCT more than 3 columns)
-- SUM (DISTINCT round_to_100(col))
-- SUM (CASE WHEN .. ..)
-- COUNT (DISTINCT ) / SUM (l_orderkey) - COUNT (DISTINCT CASE ) / SUM (l_orderkey)
-- Different ORDER BY / GROUP BY
-- Deeper subqueries
------------------------------------ TEST 1 SUM DISTINCT STARTS ---------------------------------
SELECT
l_partkey, sum_of_order_keys
FROM
(SELECT
sum(distinct l_orderkey) AS sum_of_order_keys, l_partkey
FROM
lineitem
GROUP BY
l_partkey) as repartitioned_table
ORDER BY
sum_of_order_keys, l_partkey
DESC LIMIT 10;
SELECT
l_partkey, sum_of_order_keys
FROM
(SELECT
sum(distinct l_orderkey) AS sum_of_order_keys, l_partkey
FROM
lineitem_local
GROUP BY
l_partkey) as repartitioned_table
ORDER BY
sum_of_order_keys, l_partkey
DESC LIMIT 10;
------------------------------------ TEST 1 SUM DISTINCT ENDS ---------------------------------
------------------------------------ TEST 2 - Regression tests STARTS---------------------------------
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT l_partkey)
FROM lineitem
GROUP BY l_orderkey) sub
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT l_partkey)
FROM lineitem_local
GROUP BY l_orderkey) sub
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_partkey, count(DISTINCT l_orderkey)
FROM lineitem
GROUP BY l_partkey) sub
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_partkey, count(DISTINCT l_orderkey)
FROM lineitem_local
GROUP BY l_partkey) sub
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
-- case expr in count distinct is supported.
-- count orders partkeys if l_shipmode is air
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT CASE WHEN l_shipmode = 'AIR' THEN l_partkey ELSE NULL END) as count
FROM lineitem
GROUP BY l_orderkey) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT CASE WHEN l_shipmode = 'AIR' THEN l_partkey ELSE NULL END) as count
FROM lineitem_local
GROUP BY l_orderkey) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
-- text like operator is also supported
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT CASE WHEN l_shipmode like '%A%' THEN l_partkey ELSE NULL END) as count
FROM lineitem
GROUP BY l_orderkey) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT CASE WHEN l_shipmode like '%A%' THEN l_partkey ELSE NULL END) as count
FROM lineitem_local
GROUP BY l_orderkey) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
-- count distinct is rejected if it does not reference any columns
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT 1)
FROM lineitem
GROUP BY l_orderkey) sub
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
-- count distinct is rejected if it does not reference any columns
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT (random() * 5)::int)
FROM lineitem
GROUP BY l_orderkey) sub
ORDER BY 2 DESC
LIMIT 10;
-- even non-const function calls are supported within count distinct
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT (random() * 5)::int = l_linenumber)
FROM lineitem
GROUP BY l_orderkey) sub
ORDER BY 2 DESC, 1 DESC
LIMIT 0;
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT (random() * 5)::int = l_linenumber)
FROM lineitem_local
GROUP BY l_orderkey) sub
ORDER BY 2 DESC , 1 DESC
LIMIT 0;
------------------------------------ TEST 2 Regression tests ENDS ---------------------------------
------------------------------------ TEST 3 - COUNT (DISTINCT round_to_100(col)) STARTS ---------------------------------
SELECT *
FROM (
SELECT
l_quantity, count(DISTINCT ((l_orderkey / 1000) * 1000 )) as count
FROM lineitem
GROUP BY l_quantity) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_quantity, count(DISTINCT ((l_orderkey / 1000) * 1000) ) as count
FROM lineitem_local
GROUP BY l_quantity) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer)
RETURNS text AS
$$
DECLARE
strresult text;
BEGIN
RETURN (numtimes / 100) * 100;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;
SELECT *
FROM (
SELECT
l_tax, count(DISTINCT fnsomefunc(l_orderkey::int)) as count
FROM lineitem
GROUP BY l_tax) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_tax, count(DISTINCT fnsomefunc(l_orderkey::int)) as count
FROM lineitem_local
GROUP BY l_tax) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
------------------------------------ TEST 3 - COUNT (DISTINCT round_to_100(col)) ENDS ---------------------------------
------------------------------------ TEST 4 - COUNT (DISTINCT many columns) STARTS ---------------------------------
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT CASE WHEN l_shipmode = 'AIR' THEN l_partkey ELSE l_suppkey END) as count
FROM lineitem
GROUP BY l_orderkey) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_orderkey, count(DISTINCT CASE WHEN l_shipmode = 'AIR' THEN l_partkey ELSE l_suppkey END) as count
FROM lineitem_local
GROUP BY l_orderkey) sub
WHERE count > 0
ORDER BY 2 DESC, 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT CASE WHEN l_shipmode = 'TRUCK' THEN l_partkey ELSE l_suppkey END) as count
FROM lineitem
GROUP BY l_shipdate) sub
WHERE count > 0
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT CASE WHEN l_shipmode = 'TRUCK' THEN l_partkey ELSE l_suppkey END) as count
FROM lineitem_local
GROUP BY l_shipdate) sub
WHERE count > 0
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
WHEN l_shipmode = 'AIR' THEN l_quantity
WHEN l_shipmode = 'SHIP' THEN l_discount
ELSE l_suppkey
END) as count,
l_shipdate
FROM lineitem
GROUP BY l_shipdate) sub
WHERE count > 0
ORDER BY 1 DESC, 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
WHEN l_shipmode = 'AIR' THEN l_quantity
WHEN l_shipmode = 'SHIP' THEN l_discount
ELSE l_suppkey
END) as count,
l_shipdate
FROM lineitem_local
GROUP BY l_shipdate) sub
WHERE count > 0
ORDER BY 1 DESC, 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
WHEN l_shipmode = 'AIR' THEN l_quantity
WHEN l_shipmode = 'SHIP' THEN l_discount
ELSE l_suppkey
END) as count,
l_shipmode
FROM lineitem
GROUP BY l_shipmode) sub
WHERE count > 0
ORDER BY 1 DESC, 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
WHEN l_shipmode = 'AIR' THEN l_quantity
WHEN l_shipmode = 'SHIP' THEN l_discount
ELSE l_suppkey
END) as count,
l_shipmode
FROM lineitem_local
GROUP BY l_shipmode) sub
WHERE count > 0
ORDER BY 1 DESC, 2 DESC
LIMIT 10;
------------------------------------ TEST 4 - COUNT (DISTINCT many columns) STARTS ---------------------------------
------------------------------------ TEST 5 - Multiple Aggregates STARTS ---------------------------------
SELECT *
FROM (
SELECT
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg,
l_shipmode
FROM lineitem
GROUP BY l_shipmode) sub
ORDER BY 1 DESC, 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg,
l_shipmode
FROM lineitem_local
GROUP BY l_shipmode) sub
ORDER BY 1 DESC, 2 DESC
LIMIT 10;
------------------------------------ TEST 5 - Multiple Aggregates END ---------------------------------
------------------------------------ TEST 6 - DIFFERENT GROUP BY - ORDER BY combinations STARTS ---------------------------------
SELECT *
FROM (
SELECT
l_shipdate,
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg,
l_shipmode
FROM lineitem
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_shipdate,
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg,
l_shipmode
FROM lineitem_local
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_shipdate, l_shipmode,
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg
FROM lineitem
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_shipdate, l_shipmode,
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg
FROM lineitem_local
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg, l_shipdate, l_shipmode
FROM lineitem
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg, l_shipdate, l_shipmode
FROM lineitem_local
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_suppkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg, l_shipdate, l_shipmode
FROM lineitem
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_suppkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg, l_shipdate, l_shipmode
FROM lineitem_local
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_suppkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg, l_shipdate, l_shipmode
FROM lineitem
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_suppkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg, l_shipdate, l_shipmode
FROM lineitem_local
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_suppkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg, l_shipdate, l_shipmode
FROM lineitem
GROUP BY l_shipdate, l_shipmode) sub
ORDER BY 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_suppkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg, l_shipdate, l_shipmode
FROM lineitem_local
GROUP BY l_shipdate, l_shipmode) sub
ORDER BY 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_shipdate,
l_receiptdate,
sum(l_suppkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg
FROM lineitem
GROUP BY l_shipdate, l_receiptdate) sub
ORDER BY 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
l_shipdate, l_receiptdate,
sum(l_suppkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END) as avg
FROM lineitem_local
GROUP BY l_shipdate, l_receiptdate) sub
ORDER BY 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_linenumber
WHEN l_shipmode = 'AIR' THEN l_linenumber + 10
ELSE 2
END) as avg
FROM lineitem
GROUP BY l_shipdate) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_linenumber
WHEN l_shipmode = 'AIR' THEN l_linenumber + 10
ELSE 2
END) as avg
FROM lineitem_local
GROUP BY l_shipdate) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_linenumber
WHEN l_shipmode = 'AIR' THEN l_linenumber + 10
ELSE 2
END) as avg
FROM lineitem
GROUP BY l_shipmode) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_linenumber
WHEN l_shipmode = 'AIR' THEN l_linenumber + 10
ELSE 2
END) as avg
FROM lineitem_local
GROUP BY l_shipmode) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT l_linenumber,
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_linenumber
WHEN l_shipmode = 'AIR' THEN l_linenumber + 10
ELSE 2
END) as avg
FROM lineitem
GROUP BY l_linenumber, l_shipmode) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT l_linenumber,
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_linenumber
WHEN l_shipmode = 'AIR' THEN l_linenumber + 10
ELSE 2
END) as avg
FROM lineitem_local
GROUP BY l_linenumber, l_shipmode) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT l_tax), avg(l_partkey)
FROM lineitem
GROUP BY l_orderkey) sub
ORDER BY 1 DESC, 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT l_tax), avg(l_partkey)
FROM lineitem_local
GROUP BY l_orderkey) sub
ORDER BY 1 DESC, 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT l_shipdate)
FROM lineitem
GROUP BY l_shipmode) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
count(DISTINCT l_shipdate)
FROM lineitem_local
GROUP BY l_shipmode) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM (
SELECT l_shipdate,
count(DISTINCT l_quantity)
FROM lineitem
GROUP BY l_shipdate) sub
ORDER BY 2 DESC
LIMIT 10;
SELECT *
FROM (
SELECT l_shipdate,
count(DISTINCT l_quantity)
FROM lineitem_local
GROUP BY l_shipdate) sub
ORDER BY 2 DESC
LIMIT 10;
SELECT *
FROM ( SELECT
count(DISTINCT l_quantity)
FROM lineitem
GROUP BY l_shipdate) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM ( SELECT
count(DISTINCT l_quantity)
FROM lineitem_local
GROUP BY l_shipdate) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM ( SELECT
count(DISTINCT l_quantity)
FROM lineitem
GROUP BY l_shipdate, l_shipmode) sub
ORDER BY 1 DESC
LIMIT 10;
SELECT *
FROM ( SELECT
count(DISTINCT l_quantity)
FROM lineitem_local
GROUP BY l_shipdate, l_shipmode) sub
ORDER BY 1 DESC
LIMIT 10;
------------------------------------ TEST 6 - DIFFERENT GROUP BY - ORDER BY combinations ENDS ---------------------------------
------------------------------------ TEST 7 - DEEPER SUBQUERIES STARTS ---------------------------------
SELECT (l_suppkey), (cnt) FROM
(
SELECT
l_suppkey, cnt
FROM (
SELECT
l_suppkey,
count(DISTINCT l_shipdate) as cnt
FROM
lineitem
GROUP BY
l_suppkey) z
) y ORDER BY 2 DESC, 1
LIMIT 10;
SELECT (l_suppkey), (cnt) FROM
(
SELECT
l_suppkey, cnt
FROM (
SELECT
l_suppkey,
count(DISTINCT l_shipdate) as cnt
FROM
lineitem_local
GROUP BY
l_suppkey) z
) y ORDER BY 2 DESC, 1
LIMIT 10;
SELECT l_suppkey,
sum(suppkey_count) AS total_suppkey_count
FROM
(SELECT l_suppkey,
count(DISTINCT l_tax) AS suppkey_count
FROM lineitem
GROUP BY l_suppkey
) AS distributed_table
GROUP BY l_suppkey ORDER BY 2 DESC LIMIT 5;
SELECT l_suppkey,
sum(suppkey_count) AS total_suppkey_count
FROM
(SELECT l_suppkey,
count(DISTINCT l_tax) AS suppkey_count
FROM lineitem_local
GROUP BY l_suppkey
) AS distributed_table
GROUP BY l_suppkey ORDER BY 2 DESC LIMIT 5;
select
total,
avg(avg_count) as total_avg_count
from
(select
number_sum,
count(DISTINCT l_suppkey) as total,
avg(total_count) avg_count
from
(select
l_suppkey,
sum(l_linenumber) as number_sum,
count(DISTINCT l_shipmode) as total_count
from
lineitem
where
l_partkey > 100 and
l_quantity > 2 and
l_orderkey < 10000
group by
l_suppkey) as distributed_table
where
number_sum >= 10
group by
number_sum) as distributed_table_2
group by
total
order by
total_avg_count DESC;
select
total,
avg(avg_count) as total_avg_count
from
(select
number_sum,
count(DISTINCT l_suppkey) as total,
avg(total_count) avg_count
from
(select
l_suppkey,
sum(l_linenumber) as number_sum,
count(DISTINCT l_shipmode) as total_count
from
lineitem_local
where
l_partkey > 100 and
l_quantity > 2 and
l_orderkey < 10000
group by
l_suppkey) as distributed_table
where
number_sum >= 10
group by
number_sum) as distributed_table_2
group by
total
order by
total_avg_count DESC;;
------------------------------------ TEST 7 - DEEPER SUBQUERIES ENDS -----------------------------------
------------------------------------ TEST 8 - MISC STARTS ---------------------------------
SELECT * FROM (SELECT l_orderkey, count(DISTINCT lineitem.*) from lineitem GROUP BY l_orderkey )t ORDER BY 2 DESC,1 DESC LIMIT 10;
SELECT * FROM (SELECT l_shipmode, count(DISTINCT (l_shipdate, l_tax)) from lineitem GROUP BY l_shipmode )t ORDER BY 2 DESC,1 DESC LIMIT 10;
SELECT * FROM (SELECT l_shipmode, count(DISTINCT (l_shipdate, l_tax)) from lineitem_local GROUP BY l_shipmode )t ORDER BY 2 DESC,1 DESC LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_suppkey * l_tax / 100) /
count(DISTINCT (
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END, l_shipinstruct )) as avg, l_shipdate, l_shipmode
FROM lineitem
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_suppkey * l_tax / 100) /
count(DISTINCT (
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey
ELSE l_suppkey
END, l_shipinstruct )) as avg, l_shipdate, l_shipmode
FROM lineitem_local
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 1 DESC, 2 DESC, 3 DESC
LIMIT 10;
SELECT *
FROM (
SELECT
sum(l_partkey * l_tax / 100) /
count(DISTINCT
CASE
WHEN l_shipmode = 'TRUCK' THEN l_partkey / 0
ELSE l_suppkey
END) as avg,
l_shipmode, max(l_shipinstruct)
FROM lineitem
GROUP BY l_shipmode, l_shipdate) sub
ORDER BY 3 DESC, 2 DESC, 1 DESC
LIMIT 10;
SELECT * FROM (SELECT l_shipmode, count(DISTINCT lineitem.l_partkey * l_tax / l_suppkey) from lineitem GROUP BY l_shipmode )t ORDER BY 2 DESC,1 DESC LIMIT 10;
SELECT * FROM (SELECT l_shipmode, count(DISTINCT lineitem.l_partkey * l_tax / l_suppkey) from lineitem_local as lineitem GROUP BY l_shipmode )t ORDER BY 2 DESC,1 DESC LIMIT 10;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment