论坛可视化

 select a.auditSum            as "审核通过帖子总数",
           b.allSum              as "帖子总数",
           a.auditSum / b.allSum as "近7日帖子审核通过率",
           c.currentYearSum      as "value2"
    from (
             (select count(1) as "auditSum"
              from memos
              where destroy_status is null and hidden = 0 and published_at is not null) a,
                     (select count(1) as "allSum" from memos) b,
                 (select count(1) as "currentYearSum"
                  from memos
                  where destroy_status is null
                    and hidden = 0
                    and published_at is not null
                    and DATE_FORMAT(published_at, '%Y') = YEAR(NOW())) c
     );

/* 所有帖子总数 */
select count(*) from memos;

/*已发布的帖子总数 */
select count(*) from memos 
where destroy_status is null and hidden=0 and published_at is not null and root_id is null;
/*
destroy_status=0表示已删除
hidden=1 表示隐藏，hidden=0表示未隐藏
published_at有值表示已审核通过
root_id不为空表示帖子，有值表示帖子评论
*/

select is_original as "是否原创 1原创 0非原创", count(1) as "数量" from memos 
where destroy_status is null and hidden=0 and published_at is not null
group by is_original;

select DATE_FORMAT(published_at, '%Y') as "key", COUNT(1) as "value"
from memos
where destroy_status is null
  and hidden = 0
  and published_at is not null
group by DATE_FORMAT(published_at, '%Y')
order by DATE_FORMAT(published_at, '%Y');

select id as "id",subject as "subject",published_at as "publishedAt",viewed_count as "viewedCount",praises_count as "praisesCount",replies_count as "repliesCount",(viewed_count+praises_count+replies_count) as "behavioreSum"
from memos where destroy_status is null and hidden=0 and published_at is not null and DATE_FORMAT(published_at, '%Y') = YEAR (NOW())
order by (viewed_count+praises_count+replies_count) desc
limit 0,5;

select a.ancestry as "key", fs.title as "name", a.tmp as "value"
from (select f.ancestry, count(1) as "tmp"
      from memos m
               left join forum_sections f on m.forum_section_id = f.id
      where m.destroy_status is null
        and m.hidden = 0
        and m.published_at is not null
      group by f.ancestry) a
         left join forum_sections fs on fs.id = a.ancestry
ORDER BY a.ancestry DESC;

SELECT k, COUNT(*) AS y
FROM (
    SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS k
    FROM praise_treads
    WHERE praise_tread_object_type='Memo'
      AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
      AND created_at < CURDATE()
) AS sub
GROUP BY k
ORDER BY k;

select a.currentMonth as "name", a.tag_id as "key", sum(a.countTag) as "value"
from (select DATE_FORMAT(published_at, '%m') as "currentMonth", tag_id, count(1) as "countTag"
      from memos
      where DATE_FORMAT(published_at, '%Y') = YEAR (NOW()) and destroy_status is null and hidden=0 and published_at is not null
      group by DATE_FORMAT(published_at, '%m'), tag_id
     ) a
group by a.currentMonth, a.tag_id
order by a.tag_id, a.currentMonth asc;