áááºážáá²á· Confluence áá¬áá¬ááŸá¬ áá±áá¬ááœááºááœá± áá¯ááºááœá¬ážáá°ážáá¬áž á áá°ážááœá²áá«ááá¯ááºááœá±ááᯠáááºáá°á á¡áá»á¬ážáá¯á¶áž á¡ááºáá¯ááºáá¯ááºáá² ááááá°ážáá¬ážá
ááááŸááááºá áááºááẠPostgreSQL áá¯á¶áááºážááŸá¯áá±áá¬áá±á·á áºááá¯á· áááºáá±á¬ááºááẠááá¯á¡ááºáááºá
PostgreSQL áá±áá¬áá±á·á áºááá¯ááá·áºáá»ááºááá·áº SQL query ááá¯á¡áá¯á¶ážááŒá¯ááŒááºážááŒáá·áºá áááºááẠááŒá áºáá¯á¶ááœáẠá¡áá¯á¶ážáááºáá±á¬á á¬áááºážá¡ááºážáá»á¬ážááᯠááŸá¬ááœá±ááá¯ááºáááºá
ááŒá áºáá¯á¶ááŸá áá°ážááœá²áá«ááá¯ááºá¡á¬ážáá¯á¶ážá á á¯á á¯áá±á«ááºážá¡ááœááºá¡á á¬áž-
SELECT s.spaceid,
s.spacename,
sum(LONGVAL)
FROM contentproperties c
JOIN content co ON c.contentid = co.contentid
JOIN spaces s ON co.spaceid = s.spaceid
WHERE c.contentid IN
(SELECT contentid
FROM content
WHERE contenttype = 'ATTACHMENT')
AND c.propertyname = 'FILESIZE'
GROUP BY s.spaceid
ORDER BY SUM DESC
LIMIT 5;
á¡ááœáẠ(á¡á¬áá¬áá¡áááºááᯠá¡á á¬ážááá¯ážáá¬ážáááº)
áááá¯ááºážáááºáá¬ážááŸááºážá¡áá»á¬ážá ᯠáá±á«ááºážá ááºáá¬ážáá±á¬ á á¬áá»ááºááŸá¬áá»á¬áž-
SELECT title,
MAX(VERSION)
FROM content
WHERE contenttype = 'PAGE'
GROUP BY title
ORDER BY 2 DESC
LIMIT 5;
áááá¯á¶ážáá»á¯ááº:
áááºá ááŒá áºáá¯á¶á¥ááá¬ááŸá á¡ááŒá®ážáá¯á¶áž áá°ážááœá²ááá¯ááºáá»á¬áž
SELECT DISTINCT c.contentid,
c.title AS attachmentTitle,
u.username AS uploadedBy,
co.title AS pageTitle,
cn.longval AS bytes
FROM CONTENT AS c
JOIN USER_MAPPING AS u ON u.user_key = c.creator
JOIN CONTENT AS co ON c.pageid = co.contentid
JOIN CONTENTPROPERTIES AS cn ON cn.contentid = c.contentid
WHERE c.contenttype = 'ATTACHMENT'
AND cn.longval IS NOT NULL
ORDER BY cn.longval DESC
LIMIT 5;
áááá¯á¶ážáá»á¯ááº:
ááŒááºážáá±á¬ááºážááŸá á á¬áá»ááºááŸá¬á¡áá±á¡ááœááºááŸáá·áº áá±áá¬ááœááºáá áºáá¯á¡ááœáẠááŒááºážáá±á¬ááºážááŸá á á¬áá»ááºááŸá¬á á¯á á¯áá±á«ááºáž á¡ááœááºá¡á á¬áž-
SELECT Count(content.contentid) AS number_of_trashed_pages,
Pg_size_pretty(SUM(Pg_column_size(bodycontent.BODY))) AS trash_total_size,
spaces.spacename AS space_name
FROM bodycontent
INNER JOIN content ON (content.contentid = bodycontent.contentid)
INNER JOIN spaces ON (content.spaceid = spaces.spaceid)
WHERE bodycontent.contentid IN
(SELECT contentid
FROM content
WHERE content_status = 'deleted'
AND contenttype = 'PAGE')
GROUP BY space_name
ORDER BY trash_total_size
LIMIT 5;
áááá¯á¶ážáá»á¯ááº:
á á¬áá»ááºááŸá¬á¡á¬ážáá¯á¶ážááœáẠáá¯á¶ážá áœá²áá°áá áºáŠážá á®á០áá±á«ááºážáá¯ááºáá¯ááºáá¬ážáá±á¬ áá°ážááœá²áá«ááá¯ááºáá»á¬ážá á á¯á á¯áá±á«ááºážá¡ááœááºá¡á á¬áž
SELECT u.lower_username,
sum(cp.longval) AS "size"
FROM content c1
JOIN content c2 ON c1.contentid = c2.pageid
JOIN user_mapping u ON c1.creator=u.user_key
JOIN contentproperties cp ON c2.contentid = cp.contentid
WHERE c2.contenttype='ATTACHMENT'
GROUP BY u.lower_username
ORDER BY sum(cp.longval) DESC
LIMIT 5;
áááá¯á¶ážáá»á¯ááº:
PS Confluence á¡ááœáẠá¡áá¯á¶ážáááºáá±á¬ SQL queries ááŒáá·áº ááá¯á·á áºááᯠááŒáá·áºá áœááºáá«á
source: www.habr.com