{"id":232947,"date":"2023-01-31T13:03:00","date_gmt":"2023-01-31T10:03:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232947"},"modified":"2023-02-07T13:43:26","modified_gmt":"2023-02-07T10:43:26","slug":"come-elencare-i-post-piu-votati-in-un-anno-utilizzando-sql","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/it\/come-elencare-i-post-piu-votati-in-un-anno-utilizzando-sql\/","title":{"rendered":"Come elencare i post pi\u00f9 votati in un anno utilizzando SQL?"},"content":{"rendered":"\n

In questo post<\/a>, impariamo come utilizzare SQL elencare i post pi\u00f9 popolari (in base al numero di commenti per ogni post). In questo post, otterremo alcune statistiche di base per i post nel 2016 e impareremo anche i primi 10 post ordinati in base alle votazioni.<\/p>\n

Numero totale di messaggi<\/h2>\n

Basato su questo 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
Sappiamo che ci sono 245 post pubblicati nel 2016.<\/p>\n
+----------+\n| count(1) |\n+----------+\n| \u00a0 \u00a0 \u00a0245 |\n+----------+\n1 row in set (0.01 sec)<\/code><\/pre>\n
I post pi\u00f9 discussi<\/h2>\n
E basandoci sull’SQL<\/a>, abbiamo i post pi\u00f9 discussi per quest’anno, ovvero il 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
Conosciamo i primi 10 post pi\u00f9 discussi (come i pi\u00f9 popolari) nel 2016. Modifica il limite 10<\/strong> per consentire pi\u00f9 post mostrati, ad esempio limite 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
Questi sono (al momento della scrittura):<\/p>\n
    \n
  1. Esercizio di codifica C\/C++: convertire un numero in esadecimale?<\/a><\/li>\n
  2. Programmazione Dinamica \u2013 Quanti modi per collegare i tubi?<\/a><\/li>\n
  3. Quando x==x valuta FALSE in C\/C++?<\/a><\/li>\n
  4. Come calcolare la somma di due interi senza operatori Plus+ e Minus-?<\/a><\/li>\n
  5. Come trovare l’intersezione di due array in C++?<\/a><\/li>\n
  6. Manipolazione dei bit: come impostare tutti i bit tra i e j in N uguali a M?<\/a><\/li>\n
  7. Come controllare un anagramma valido in C\/C++?<\/a><\/li>\n
  8. Esercizio di codifica C++ \u2013 Numero di 1 bit (rivisitato)<\/a><\/li>\n
  9. Programmazione dinamica \u2013 Interruzione di numeri interi<\/a><\/li>\n
  10. Esercizio di codifica C\/C++ \u2013 Trova il numero duplicato<\/a><\/li>\n<\/ol>\n
    Post pi\u00f9 votati tramite SQL<\/h2>\n
    Ora possiamo combinare le statistiche di voto da GD Star Rating<\/a> Plugin e ottenere un’altra classifica interessante.<\/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
    Questo d\u00e0 i seguenti 10 post pi\u00f9 votati dell’anno:<\/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. Quando x==x valuta FALSE in C\/C++?<\/a><\/li>\n
    2. Come trovare il massimo di due numeri interi senza utilizzare l’operatore di confronto?<\/a><\/li>\n
    3. Come eliminare un nodo da un albero di ricerca binario?<\/a><\/li>\n
    4. Esercizio di codifica C++: come verificare se un intero grande \u00e8 divisibile per 11?<\/a><\/li>\n
    5. Domanda dell’intervista Microsoft: ottieni l’area del triangolo<\/a><\/li>\n
    6. PPAP in C++ e Javascript per principianti<\/a><\/li>\n
    7. Come invertire un albero binario in C\/C++?<\/a><\/li>\n
    8. Come invertire i bit per un intero senza segno a 32 bit in C\/C++?<\/a><\/li>\n
    9. Esercizio di codifica C++: come trovare il primo numero mancante?<\/a><\/li>\n
    10. Programmazione dinamica \u2013 Interruzione di numeri interi<\/a><\/li>\n<\/ol>\n
      Naturalmente, \u00e8 possibile modificare entrambe le query SQL<\/a> di cui sopra in modo da fornire 12 post, ovvero uno dei migliori per ogni mese. Devi solo raggruppare per mese in questo modo:<\/p>\n
      GROUP BY date_format(`p`.post_date_gmt, \"%M\")<\/code><\/pre>\n
      Inoltre, potresti creare una pagina PHP<\/a> che fornisce le tabelle di ranking pi\u00f9 aggiornate per i post, ma ci\u00f2 che \u00e8 stato mostrato qui \u00e8 statico, ovvero la tabella di ranking viene generata come \u00e8 oggi.<\/p>\n
      SQL<\/p>\n
      Fonte di registrazione:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
      Come elencare i post pi\u00f9 votati in un anno utilizzando 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":[918,896,835,996,804,844,865],"tags":[1168],"class_list":["post-232947","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-altro","category-codice","category-guida-per-principianti","category-marketing-dei-contenuti","category-php-6","category-tutorial","category-wordpress-6","tag-affiai-it"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/posts\/232947","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/comments?post=232947"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/posts\/232947\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/media\/224083"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/media?parent=232947"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/categories?post=232947"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/it\/wp-json\/wp\/v2\/tags?post=232947"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}