72 High-Function Business Intelligence in e-business
SELECT MESH.ROOT_LEVEL, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS
FROM FACT1_SUBSET, MESH
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID
GROUP BY MESH.ROOT_LEVEL
Query 4:
SELECT AUTHOR.AUTHOR_NAME, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS
FROM FACT1_SUBSET, MESH, AUTHOR
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND
FACT1_SUBSET.AUTHOR_ID=AUTHOR.AUTHOR_ID AND
MESH.ROOT_LEVEL='Anatomy' AND MESH.FIRST_LEVEL='Animal Structures'
GROUP BY AUTHOR.AUTHOR_NAME
Query 5:
SELECT AUTHOR.AUTHOR_NAME, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS
FROM FACT1_SUBSET, MESH, AUTHOR
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND
FACT1_SUBSET.AUTHOR_ID=AUTHOR.AUTHOR_ID AND
MESH.ROOT_LEVEL='Anatomy' AND MESH.FIRST_LEVEL IN ('Body Regions','Cells')
GROUP BY AUTHOR.AUTHOR_NAME
Query 6:
WITH DT AS
(
SELECT COUNTRY_NAME, COUNT(*) AS COUNT, YEAR, MONTH,
ROOT_LEVEL, FIRST_LEVEL
FROM FACT1_SUBSET, MESH, TIME, COUNTRY
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND
FACT1_SUBSET.TIME_ID=TIME.TIME_ID AND
FACT1_SUBSET.COUNTRY_ID=COUNTRY.COUNTRY_ID
GROUP BY ROLLUP(YEAR, MONTH), ROLLUP(ROOT_LEVEL, FIRST_LEVEL), COUNTRY_NAME
)
SELECT *
FROM DT
ORDER BY COUNTRY_NAME, YEAR, MONTH, ROOT_LEVEL, FIRST_LEVEL
Query 7:
WITH DT AS
(
SELECT COUNTRY_NAME, COUNT(*) AS COUNT, YEAR, ROOT_LEVEL,
FROM FACT1_SUBSET, MESH, TIME, COUNTRY
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND
FACT1_SUBSET.TIME_ID=TIME.TIME_ID AND
FACT1_SUBSET.COUNTRY_ID=COUNTRY.COUNTRY_ID
GROUP BY ROLLUP(YEAR), ROLLUP(ROOT_LEVEL), COUNTRY_NAME
)
SELECT *
FROM DT
ORDER BY COUNTRY_NAME, YEAR, ROOT_LEVEL