{"id":232908,"date":"2023-01-31T13:35:00","date_gmt":"2023-01-31T10:35:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232908"},"modified":"2023-02-07T13:05:24","modified_gmt":"2023-02-07T10:05:24","slug":"kuinka-luetella-vuoden-eniten-aeaenestaeneet-viestit-sql-n-avulla","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/fi\/kuinka-luetella-vuoden-eniten-aeaenestaeneet-viestit-sql-n-avulla\/","title":{"rendered":"Kuinka luetella vuoden eniten \u00e4\u00e4nest\u00e4neet viestit SQL:n avulla?"},"content":{"rendered":"\n

T\u00e4ss\u00e4 viestiss\u00e4<\/a> opimme k\u00e4ytt\u00e4m\u00e4\u00e4n SQL:\u00e4\u00e4 luettelosta suosituimmat viestit (kunkin viestien kommenttien m\u00e4\u00e4r\u00e4n mukaan). T\u00e4st\u00e4 postauksesta saamme joitain perustilastoja vuoden 2016 viesteist\u00e4, ja my\u00f6s, opimme huonosti 10 parasta viesti\u00e4, jotka on lajiteltu \u00e4\u00e4nestysten perusteella.<\/p>\n

Viestien kokonaism\u00e4\u00e4r\u00e4<\/h2>\n

T\u00e4m\u00e4n SQL<\/a> :n perusteella :<\/p>\n

SELECT \n\u00a0 count(1) \nFROM \n\u00a0 wp_posts\nWHERE \n\u00a0 post_type = \"post\" and \n\u00a0 post_status = \"publish\" and \n\u00a0 date_format(post_date_gmt, \"%Y\") = \"2016\"<\/code><\/pre>\n
Tied\u00e4mme, ett\u00e4 vuonna 2016 on julkaistu 245 viesti\u00e4.<\/p>\n
+----------+\n| count(1) |\n+----------+\n| \u00a0 \u00a0 \u00a0245 |\n+----------+\n1 row in set (0.01 sec)<\/code><\/pre>\n
Suosituimmat keskustelunaiheet<\/h2>\n
Ja SQL:n<\/a> perusteella meill\u00e4 on t\u00e4m\u00e4n vuoden, eli 2016, eniten keskusteltu viestit.<\/p>\n
SELECT \n\u00a0 post_name, post_title \nFROM \n\u00a0 wp_posts \nWHERE \n\u00a0 post_type = 'post' and \n\u00a0 post_status = 'publish' and\n\u00a0 date_format(post_date_gmt, \"%Y\") = '2016' \nORDER BY\n\u00a0comment_count DESC \nLIMIT 10<\/code><\/pre>\n
Tied\u00e4mme 10 eniten keskusteltua (suosituimpana) viesti\u00e4 vuonna 2016. Muuta rajaa 10<\/strong> salliaksesi enemm\u00e4n viestej\u00e4, esim. raja 20.<\/p>\n
+--------------------------------------------------------------------------------+------------------------------------------------------------------------+\n| post_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| post_title \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n+--------------------------------------------------------------------------------+------------------------------------------------------------------------+\n| cc-coding-exercise-convert-a-number-to-hexadecimal \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | C\/C++ Coding Exercise - Convert a Number to Hexadecimal? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| dynamic-programming-how-many-ways-to-connect-the-pipes \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Dynamic Programming - How many ways to connect the pipes? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n| when-xx-evaluates-to-false-in-cc \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | When x==x Evaluates to FALSE in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| how-to-compute-sum-of-two-integers-without-plus-and-minus-operators \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| How to Compute Sum of Two Integers without Plus+ and Minus- Operators? |\n| how-to-find-intersection-of-two-arrays-in-c \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| How to Find Intersection of Two Arrays in C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| bit-manipulation-how-to-set-all-bits-between-i-and-j-in-n-equal-to-m-cplusplus | Bit Manipulation: How to Set All Bits Between i and j in N equal to M? |\n| how-to-check-valid-anagram-in-cc \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | How to Check Valid Anagram in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| c-coding-exercise-number-of-1-bits-revisited \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | C++ Coding Exercise - Number of 1 Bits (Revisited) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| dynamic-programming-integer-break \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| Dynamic Programming - Integer Break \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n| cc-coding-exercise-find-the-duplicate-number \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | C\/C++ Coding Exercise - Find the Duplicate Number \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n+--------------------------------------------------------------------------------+------------------------------------------------------------------------+\n10 rows in set (0.01 sec)<\/code><\/pre>\n
N\u00e4m\u00e4 ovat (kirjoitettaessa):<\/p>\n
    \n
  1. C\/C++-koodausharjoitus \u2013 Muunna luku heksadesimaaliksi?<\/a><\/li>\n
  2. Dynaaminen ohjelmointi \u2013 Kuinka monta tapaa yhdist\u00e4\u00e4 putket?<\/a><\/li>\n
  3. Kun x==x on arvoltaan EP\u00c4TOSI C\/C++:ssa?<\/a><\/li>\n
  4. Kuinka laskea kahden kokonaisluvun summa ilman plus+- ja miinus-operaattoreita?<\/a><\/li>\n
  5. Kuinka l\u00f6yt\u00e4\u00e4 kahden taulukon leikkauspiste C++:ssa?<\/a><\/li>\n
  6. Bittimanipulaatio: Kuinka asettaa kaikki bitit i:n ja j:n v\u00e4lill\u00e4 N:ss\u00e4 yht\u00e4 suureksi kuin M?<\/a><\/li>\n
  7. Kuinka tarkistaa kelvollinen anagrammi C\/C++:ssa?<\/a><\/li>\n
  8. C++-koodausharjoitus \u2013 1 bitin m\u00e4\u00e4r\u00e4 (tarkastettu uudelleen)<\/a><\/li>\n
  9. Dynaaminen ohjelmointi \u2013 kokonaislukumurto<\/a><\/li>\n
  10. C\/C++-koodausharjoitus \u2013 Etsi kaksoisnumero<\/a><\/li>\n<\/ol>\n
    Eniten \u00e4\u00e4nest\u00e4neet viestit SQL:n kautta<\/h2>\n
    Nyt voimme yhdist\u00e4\u00e4 GD Star Rating<\/a> Pluginin \u00e4\u00e4nestystilastot ja saada toisen mielenkiintoisen sijoituksen.<\/p>\n
    SELECT \n\u00a0 \u00a0`p`.`ID`, \n\u00a0 \u00a0`p`.`post_title` as `title`, \n\u00a0 \u00a0`visitor_votes` + `user_votes` as `total_votes`, \n\u00a0 \u00a0`visitor_votes`, \n\u00a0 \u00a0`user_votes` \u00a0\nFROM \u00a0\n\u00a0 `wp_gdsr_data_article` as `da` \nINNER JOIN \n\u00a0 `wp_posts` as `p` ON `da`.`post_id` = `p`.`ID` \nWHERE\n\u00a0 `p`.post_type = 'post' and \n\u00a0 `p`.post_status = 'publish' and \u00a0\n\u00a0 date_format(`p`.`post_date_gmt`, \"%Y\") = '2016'\nHAVING\n\u00a0 `total_votes` > 0\nORDER BY\n\u00a0 `total_votes` desc \nLIMIT 10<\/code><\/pre>\n
    T\u00e4m\u00e4 antaa seuraavat 10 eniten \u00e4\u00e4nestetty\u00e4 viesti\u00e4 vuonna:<\/p>\n
    +------+----------------------------------------------------------------------------+-------------+---------------+------------+\n| ID \u00a0 | title \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| total_votes | visitor_votes | user_votes |\n+------+----------------------------------------------------------------------------+-------------+---------------+------------+\n| 4793 | When x==x Evaluates to FALSE in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 105.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a095.0 | \u00a0 \u00a0 \u00a0 10.0 |\n| 4679 | How to Find the Maximum of Two Integers without Using Comparison Operator? | \u00a0 \u00a0 \u00a0 \u00a081.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a081.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4191 | How to Delete a Node from a Binary Search Tree? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a068.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a058.0 | \u00a0 \u00a0 \u00a0 10.0 |\n| 4380 | C++ Coding Exercise: How to Check if a Large Integer is divisible by 11? \u00a0 | \u00a0 \u00a0 \u00a0 \u00a067.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a057.0 | \u00a0 \u00a0 \u00a0 10.0 |\n| 4329 | Microsoft Interview Question - Get the Area of the Triangle \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a066.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a066.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4740 | PPAP in C++ and Javascript for Beginner \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a062.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a052.0 | \u00a0 \u00a0 \u00a0 10.0 |\n| 4166 | How to Invert a Binary Tree in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a048.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a048.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4176 | How to Reverse Bits for 32-bit Unsigned Integer in C\/C++? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a046.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a046.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4178 | C++ Coding Exercise - How to Find First Missing Number? \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a040.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a040.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n| 4227 | Dynamic Programming - Integer Break \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a031.0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a031.0 | \u00a0 \u00a0 \u00a0 \u00a00.0 |\n+------+----------------------------------------------------------------------------+-------------+---------------+------------+\n10 rows in set (0.01 sec)<\/code><\/pre>\n
      \n
    1. Kun x==x on arvoltaan EP\u00c4TOSI C\/C++:ssa?<\/a><\/li>\n
    2. Kuinka l\u00f6yt\u00e4\u00e4 enint\u00e4\u00e4n kaksi kokonaislukua ilman vertailuoperaattoria?<\/a><\/li>\n
    3. Kuinka poistaa solmu bin\u00e4\u00e4rihakupuusta?<\/a><\/li>\n
    4. C++-koodausharjoitus: Kuinka tarkistaa, onko suuri kokonaisluku jaollinen 11:ll\u00e4?<\/a><\/li>\n
    5. Microsoftin haastattelukysymys \u2013 Hanki kolmion pinta-ala<\/a><\/li>\n
    6. PPAP C++:ssa ja Javascript aloittelijoille<\/a><\/li>\n
    7. Kuinka k\u00e4\u00e4nt\u00e4\u00e4 bin\u00e4\u00e4ripuu C\/C++:ssa?<\/a><\/li>\n
    8. Kuinka k\u00e4\u00e4nt\u00e4\u00e4 bittej\u00e4 32-bittiselle etumerkitt\u00f6m\u00e4lle kokonaisluvulle C\/C++:ssa?<\/a><\/li>\n
    9. C++-koodausharjoitus \u2013 Kuinka l\u00f6yt\u00e4\u00e4 ensimm\u00e4inen puuttuva numero?<\/a><\/li>\n
    10. Dynaaminen ohjelmointi \u2013 kokonaislukumurto<\/a><\/li>\n<\/ol>\n
      Tietysti voit muokata yll\u00e4 olevia molempia SQL-kyselyj\u00e4<\/a> niin, ett\u00e4 se antaa 12 viesti\u00e4 eli yhden parhaan jokaiselle kuukaudelle. Sinun tarvitsee vain ryhmitell\u00e4 kuukauden mukaan seuraavasti:<\/p>\n
      GROUP BY date_format(`p`.post_date_gmt, \"%M\")<\/code><\/pre>\n
      Ja my\u00f6s, voit luoda PHP<\/a> -sivun, joka antaa uusimmat rankingtaulukot vireille, mutta t\u00e4ss\u00e4 n\u00e4kyv\u00e4t ovat staattisia eli rankingtaulukko luodaan sellaisena kuin se on t\u00e4n\u00e4\u00e4n.<\/p>\n
      SQL<\/p>\n
      :  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
      Kuinka luetella vuoden eniten \u00e4\u00e4nest\u00e4neet viestit SQL:n avulla?<\/p>\n","protected":false},"author":1,"featured_media":224083,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_wp_rev_ctl_limit":""},"categories":[895,917,834,843,803,995,864],"tags":[1166],"class_list":["post-232908","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-koodi","category-muut","category-opas-aloittelijoille","category-opetusohjelmia","category-php-5","category-sisaltomarkkinointi","category-wordpress-5","tag-affiai-fi"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts\/232908","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/comments?post=232908"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts\/232908\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/media\/224083"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/media?parent=232908"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/categories?post=232908"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/tags?post=232908"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}