{"id":232904,"date":"2023-01-31T13:48:00","date_gmt":"2023-01-31T10:48:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232904"},"modified":"2023-02-07T13:11:02","modified_gmt":"2023-02-07T10:11:02","slug":"jak-wyswietlic-liste-najczesciej-glosowanych-postow-w-ciagu-roku-za-pomoca-sql","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/pl\/jak-wyswietlic-liste-najczesciej-glosowanych-postow-w-ciagu-roku-za-pomoca-sql\/","title":{"rendered":"Jak wy\u015bwietli\u0107 list\u0119 najcz\u0119\u015bciej g\u0142osowanych post\u00f3w w ci\u0105gu roku za pomoc\u0105 SQL?"},"content":{"rendered":"\n

W tym po\u015bcie<\/a> dowiemy si\u0119, jak u\u017cywa\u0107 SQL listy najpopularniejszych post\u00f3w (wed\u0142ug liczby komentarzy dla ka\u017cdego posta). Z tych post\u00f3w otrzymamy podstawowe statystyki post\u00f3w w 2016 roku, a tak\u017ce poznamy 10 najlepszych post\u00f3w posortowanych wed\u0142ug g\u0142os\u00f3w.<\/p>\n

Ca\u0142kowita liczba post\u00f3w<\/h2>\n

Na podstawie tego SQL<\/a> :<\/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
Wiemy, \u017ce w 2016 roku opublikowano 245 post\u00f3w.<\/p>\n
+----------+\n| count(1) |\n+----------+\n| \u00a0 \u00a0 \u00a0245 |\n+----------+\n1 row in set (0.01 sec)<\/code><\/pre>\n
Najcz\u0119\u015bciej komentowane posty<\/h2>\n
I na podstawie SQL<\/a> mamy najbardziej dyskutowane posty na ten rok, czyli 2016.<\/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
Znamy 10 najcz\u0119\u015bciej komentowanych (jako najbardziej popularnych) post\u00f3w w 2016 r. Zmie\u0144 limit 10<\/strong>, aby umo\u017cliwi\u0107 wy\u015bwietlanie wi\u0119kszej liczby post\u00f3w, np. limit 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
S\u0105 to (w momencie pisania):<\/p>\n
    \n
  1. \u0106wiczenie z kodowania C\/C++ \u2013 Konwersja liczby na szesnastkow\u0105?<\/a><\/li>\n
  2. Programowanie dynamiczne \u2013 Ile sposob\u00f3w \u0142\u0105czenia rur?<\/a><\/li>\n
  3. Kiedy x==x zwraca warto\u015b\u0107 FALSE w C\/C++?<\/a><\/li>\n
  4. Jak obliczy\u0107 sum\u0119 dw\u00f3ch liczb ca\u0142kowitych bez operator\u00f3w plus+ i minus-?<\/a><\/li>\n
  5. Jak znale\u017a\u0107 przeci\u0119cie dw\u00f3ch tablic w C++?<\/a><\/li>\n
  6. Manipulacja bitami: jak ustawi\u0107 wszystkie bity mi\u0119dzy i i j w N r\u00f3wne M?<\/a><\/li>\n
  7. Jak sprawdzi\u0107 poprawny anagram w C\/C++?<\/a><\/li>\n
  8. \u0106wiczenie z kodowania C++ \u2013 liczba 1 bit\u00f3w (powtarzane)<\/a><\/li>\n
  9. Programowanie dynamiczne \u2013 \u0142amanie liczb ca\u0142kowitych<\/a><\/li>\n
  10. \u0106wiczenie z kodowania C\/C++ \u2013 Znajd\u017a zduplikowany numer<\/a><\/li>\n<\/ol>\n
    Wi\u0119kszo\u015b\u0107 g\u0142osowanych post\u00f3w za po\u015brednictwem SQL<\/h2>\n
    Teraz mo\u017cemy po\u0142\u0105czy\u0107 statystyki g\u0142osowania z wtyczki GD Star Rating<\/a> Plugin i uzyska\u0107 kolejne ciekawe rankingi.<\/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
    Daje to 10 najcz\u0119\u015bciej g\u0142osowanych post\u00f3w w roku:<\/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. Kiedy x==x zwraca warto\u015b\u0107 FALSE w C\/C++?<\/a><\/li>\n
    2. Jak znale\u017a\u0107 maksimum dw\u00f3ch liczb ca\u0142kowitych bez u\u017cycia operatora por\u00f3wnania?<\/a><\/li>\n
    3. Jak usun\u0105\u0107 w\u0119ze\u0142 z drzewa wyszukiwania binarnego?<\/a><\/li>\n
    4. Zadanie z kodowania w C++: Jak sprawdzi\u0107, czy du\u017ca liczba ca\u0142kowita jest podzielna przez 11?<\/a><\/li>\n
    5. Pytanie do rozmowy kwalifikacyjnej Microsoft \u2014 Uzyskaj obszar tr\u00f3jk\u0105ta<\/a><\/li>\n
    6. PPAP w C++ i JavaScript dla pocz\u0105tkuj\u0105cych<\/a><\/li>\n
    7. Jak odwr\u00f3ci\u0107 drzewo binarne w C\/C++?<\/a><\/li>\n
    8. Jak odwr\u00f3ci\u0107 bity dla 32-bitowej liczby ca\u0142kowitej bez znaku w C\/C++?<\/a><\/li>\n
    9. \u0106wiczenie z kodowania w C++ \u2013 jak znale\u017a\u0107 pierwsz\u0105 brakuj\u0105c\u0105 liczb\u0119?<\/a><\/li>\n
    10. Programowanie dynamiczne \u2013 \u0142amanie liczb ca\u0142kowitych<\/a><\/li>\n<\/ol>\n
      Oczywi\u015bcie mo\u017cesz zmodyfikowa\u0107 oba powy\u017csze zapytania SQL<\/a> tak, aby dawa\u0142y 12 post\u00f3w, czyli jeden najlepszy na ka\u017cdy miesi\u0105c. Wystarczy pogrupowa\u0107 wed\u0142ug miesi\u0105ca w ten spos\u00f3b:<\/p>\n
      GROUP BY date_format(`p`.post_date_gmt, \"%M\")<\/code><\/pre>\n
      Mo\u017cesz tak\u017ce utworzy\u0107 stron\u0119 PHP<\/a>, kt\u00f3ra zawiera najbardziej aktualne tabele rankingowe dla post\u00f3w, ale to, co zosta\u0142o tutaj pokazane, jest statyczne, tj. tabela rankingowa jest generowana tak, jak jest dzisiaj.<\/p>\n
      SQL<\/p>\n
      \u0179r\u00f3d\u0142o nagrywania:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
      Jak wy\u015bwietli\u0107 list\u0119 najcz\u0119\u015bciej g\u0142osowanych post\u00f3w w ci\u0105gu roku za pomoc\u0105 SQL?<\/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":[919,897,997,805,836,845,866],"tags":[1169],"class_list":["post-232904","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-inny","category-kod","category-marketing-tresci","category-php-7","category-przewodnik-dla-poczatkujacych","category-samouczki","category-wordpress-7","tag-affiai-pl"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts\/232904","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/comments?post=232904"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts\/232904\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/media\/224083"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/media?parent=232904"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/categories?post=232904"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/tags?post=232904"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}