BigDataProject/3.6.cpp

#include "MySQLWrapper.h"
#include <iostream>
#include <sstream>
#include <vector>
#include <map>
#include <set>
#include <cstdlib>
#include <cstdio>
#include <cstring>
#include <algorithm>
#include <fstream>
using namespace std;

#include "local_db_account.h"
#include "util.hpp"

int main()
{
    /// 3.6 计算机专业不同编程语言平均薪资柱状图. Based on 3.5 code.

    MySQLConn conn;
    conn.connect("127.0.0.1",db_user,db_passwd,"bigdata3",3306);
    cout<<"Connected to DB"<<endl;
    conn.exec("set names gbk",nullptr);

    /// Based on TIOBE 2017
    vector<vector<string>> langs
    {
        { "Java" },
        { "C++" },
        { "C#" },
        { "Python", "Py" },
        { "PHP" } ,
        { "Javascript", "JS" },
        { "Swift" },
        { "Ruby" },
        { "Perl" },
        { "Visual Basic","VisualBasic","VB" },
        { "Go" },
        { "MATLAB" }
    };

    map<string,int> mp;

    int szLang=langs.size();
    for(int i=0;i<szLang;i++)
    {
        int szAlias=langs[i].size();
        for(int j=0;j<szAlias;j++)
        {
            ostringstream ostr;
            ostr<<"select count(*) from lagou_job where ((title like '%"
            <<langs[i][j]<<"%') or (tags like '%"
            <<langs[i][j]<<"%'))";

            string sqlcmd=ostr.str();

            auto fn1=[&](const string& SQLCMD) {
                conn.exec(SQLCMD,[&](MySQLResult& res)
                      {
                          res.stepRow([&](char** val,unsigned long* len)
                                      {
                                          int cc=ParseInt(val[0]);

                                          cout<<SQLCMD<<" "<<langs[i][0]<<"-"<<cc<<endl;

                                          auto iter=mp.find(langs[i][0]);
                                          if(iter!=mp.end())
                                          {
                                              iter->second += cc;
                                          }
                                          else
                                          {
                                              mp.insert(make_pair(langs[i][0],cc));
                                          }
                                      });
                      });
            };

            conn.exec("use bigdata3",nullptr);
            fn1(sqlcmd);

            ostr.str("");
            ostr<<"select count(*) from pages where ((title like '%"
            <<langs[i][j]<<"%') or (tags like '%"
            <<langs[i][j]<<"%'))";

            sqlcmd=ostr.str();

            conn.exec("use bigdata5",nullptr);
            fn1(sqlcmd);

            ostr.str("");
            ostr<<"select count(*) from pages where title like '%"<<langs[i][j]<<"%' ";
            sqlcmd=ostr.str();

            conn.exec("use bigdata2",nullptr);
            fn1(sqlcmd);
        }
    }

    vector<pair<string,int>> vec;
    vec.insert(vec.end(),mp.begin(),mp.end()); /// ??? Not Naive Code?
    sort(vec.begin(),vec.end(),[](const pair<string,int>& a,const pair<string,int>& b)
         {
             return a.second > b.second;
         });

    /// ----------- Above : 3.5 --------------
    /// ----------- Below : 3.6 --------------

    auto fn_getindex=[&](const string& LangMainLable)
    {
        int sz=langs.size();
        for(int i=0;i<sz;i++)
        {
            if(langs[i][0]==LangMainLable)
            {
                return i;
            }
        }
        return -1;
    };

    map<string,vector<cash_pack>> cashmp;

    int szLoop=min(10u,vec.size());
    for(int i=0;i<szLoop;i++)
    {
        int idx=fn_getindex(vec[i].first);

        /// Search for salary.
        int szAlias=langs[idx].size();
        for(int j=0;j<szAlias;j++)
        {
            ostringstream ostr;
            ostr<<"select salary from lagou_job where ((title like '%"
            <<langs[idx][j]<<"%') or (tags like '%"
            <<langs[idx][j]<<"%'))";

            string sqlcmd=ostr.str();

            conn.exec("use bigdata3",nullptr);
            conn.exec(sqlcmd,[&](MySQLResult& res)
                      {
                          res.stepRow([&](char** val,unsigned long* len)
                                      {
                                          cash_pack pk=ParseCash(val[0]);
                                          cashmp[vec[i].first].push_back(pk);
                                      });
                      });



            ostr<<"select salary from pages where ((title like '%"
            <<langs[idx][j]<<"%') or (tags like '%"
            <<langs[idx][j]<<"%'))";

            sqlcmd=ostr.str();
            conn.exec("use bigdata5",nullptr);
            conn.exec(sqlcmd,[&](MySQLResult& res)
                      {
                          res.stepRow([&](char** val,unsigned long* len)
                                      {
                                          cash_pack pk=ParseCashX(val[0]);
                                          cashmp[vec[i].first].push_back(pk);
                                      });
                      });

            ostr<<"select salary from pages where title like '%"<<langs[idx][j]<<"%' ";
            sqlcmd=ostr.str();
            conn.exec("use bigdata2",nullptr);
            conn.exec(sqlcmd,[&](MySQLResult& res)
                      {
                          res.stepRow([&](char** val,unsigned long* len)
                                      {
                                          cash_pack pk=ParseCashX(val[0]);
                                          cashmp[vec[i].first].push_back(pk);
                                      });
                      });
        }
    }

    ofstream ofs("result/3.6.csv");
    #define cout ofs
    for(int i=0;i<szLoop;i++)
    {
        int avLow=0;
        int avHigh=0;
        int avMid=0;

        vector<cash_pack>& vecref=cashmp[vec[i].first];
        int szVec=vecref.size();
        for(const auto& ky:vecref)
        {
            avLow+=ky.low;
            avHigh+=ky.high;
        }

        if(szVec!=0)
        {
            avLow/=szVec;
            avHigh/=szVec;
            avMid=(avLow+avHigh)/2;
        }

        cout<<vec[i].first<<","<<avLow<<","<<avHigh<<","<<avMid<<endl;
    }
    #undef cout

    return 0;
}