āĻāĻĒāĻ¨āĻžāĻ° āĻāĻ¨āĻĢā§āĻ˛ā§āĻ¯āĻŧā§āĻ¨ā§āĻ¸ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§ āĻāĻŋ āĻāĻāĻ¨āĻ āĻ¸ā§āĻĨāĻžāĻ¨ āĻĢā§āĻ°āĻŋāĻ¯āĻŧā§ āĻā§āĻā§ āĻāĻŦāĻ āĻāĻĒāĻ¨āĻŋ āĻāĻžāĻ¨ā§āĻ¨ āĻ¨āĻž āĻā§ āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻŦā§āĻļāĻŋ āĻ¸āĻāĻ¯ā§āĻā§āĻ¤āĻŋ āĻāĻĒāĻ˛ā§āĻĄ āĻāĻ°ā§?
āĻā§āĻāĻā§ āĻŦā§āĻ° āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻāĻĒāĻ¨āĻžāĻ° PostgreSQL āĻ¸āĻā§āĻāĻŽ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§ āĻ ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨āĨ¤
āĻāĻāĻāĻŋ PostgreSQL āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§āĻ° āĻŦāĻŋāĻ°ā§āĻĻā§āĻ§ā§ āĻāĻāĻāĻŋ SQL āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§, āĻāĻĒāĻ¨āĻŋ āĻāĻ¨āĻĢā§āĻ˛ā§āĻ¯āĻŧā§āĻ¨ā§āĻ¸ā§āĻ° āĻĻāĻ°āĻāĻžāĻ°ā§ āĻĒāĻ°āĻŋāĻ¸āĻāĻā§āĻ¯āĻžāĻ¨ āĻā§āĻāĻā§ āĻĒā§āĻ¤ā§ āĻĒāĻžāĻ°ā§āĻ¨āĨ¤
āĻ¸āĻā§āĻāĻŽā§ āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻ¸āĻāĻ¯ā§āĻā§āĻ¤āĻŋāĻ° āĻŽā§āĻ āĻāĻāĻžāĻ°:
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 āĻāĻ¨āĻĢā§āĻ˛ā§āĻ¯āĻŧā§āĻ¨ā§āĻ¸ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻĻāĻ°āĻāĻžāĻ°ā§ āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻā§āĻ¯āĻŧā§āĻ°āĻŋ āĻ¸āĻš āĻĒā§āĻ¸ā§āĻāĻāĻŋ āĻĒāĻ°āĻŋāĻĒā§āĻ°āĻ āĻāĻ°ā§āĻ¨
āĻāĻ¤ā§āĻ¸: www.habr.com