<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.screen.mapper.ZongzhiScreenOpinionMapper">


    <select id="getSentimentType" resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentTypeResponse">
        select count(*) as count , type,date_time as publishTime
        from tc_yq_zs
        GROUP BY type,date_time
    </select>
    <select id="getSentimenCount" resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentCountResponse">
        select count(*) as count ,sentiment_type as type,ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pt
        from tc_network_sentiment
        where sentiment_type !='2'
        GROUP BY sentiment_type
    </select>
    <select id="getSentimenClassificationBymediaType"
            resultType="com.ruoyi.screen.domain.response.ZongzhiSentimenClassificationResponse">
        select media_type as type,sum(media_count)as pt
        from tc_yq_fl where  media_type is not null
        GROUP BY media_type
    </select>
    <select id="getSentimenClassificationByeventType"
            resultType="com.ruoyi.screen.domain.response.ZongzhiSentimenClassificationResponse">
        select event_type as type, sum(event_count)as pt
        from tc_yq_fl where  event_type is not null
        GROUP BY event_type
    </select>
    <select id="getWordCloud" resultType="com.ruoyi.screen.domain.response.ZongzhiSentimenWordCloudResponse">
        select yuci_type AS type
        from tc_network_sentiment
        GROUP BY yuci_type
    </select>
    <select id="getSentimentReport"
            resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentReportResponse">
        select a.reminder, b.isturn
        from (select count(reminder) as reminder from tc_network_sentiment where reminder ='1') a,
             (select count(*) as isturn from tc_network_sentiment where isturn = '1') b

    </select>
    <select id="getSentimentReportbyReportType"
            resultType="com.ruoyi.screen.domain.response.ZongzhiReportResponse">
        select count(sentiment_report_type) as count, sentiment_report_type as sentimentReportType
        from tc_network_sentiment where sentiment_report_type is not null
        GROUP BY sentiment_report_type
    </select>
    <select id="getSentimentDept" resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentDeptResponse">
        select dep_name, count(*) as count
        from tc_network_sentiment
        where isturn ='1'
        GROUP BY dep_name
    </select>
    <select id="getSentimentFiltering"
            resultType="com.ruoyi.screen.domain.response.ZongzhiSentimentfilterResponse">
        select a.count1,b.count2,c.count3,d.pt from
        (select count(*) as count1 from  tc_network_sentiment  where sentiment_type=6)a,
        (select count(*)  as count2 from  tc_network_sentiment  where sentiment_type=7)b,
        (select count(*)  as count3 from tc_network_sentiment  where sentiment_type=8)c,
        (select ROUND(COUNT(case when sentiment_type=8 then sentiment_type else null end) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pt  from tc_network_sentiment)d

    </select>
</mapper>