{"id":232892,"date":"2023-01-31T13:40:00","date_gmt":"2023-01-31T10:40:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232892"},"modified":"2023-02-07T13:27:23","modified_gmt":"2023-02-07T10:27:23","slug":"como-enumerar-las-publicaciones-mas-votadas-en-un-ano-usando-sql","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/es\/como-enumerar-las-publicaciones-mas-votadas-en-un-ano-usando-sql\/","title":{"rendered":"\u00bfC\u00f3mo enumerar las publicaciones m\u00e1s votadas en un a\u00f1o usando SQL?"},"content":{"rendered":"\n

En esta publicaci\u00f3n<\/a>, aprendemos c\u00f3mo usar SQL para enumerar las publicaciones m\u00e1s populares (seg\u00fan la cantidad de comentarios para cada publicaci\u00f3n). En estas publicaciones, obtendremos algunas estad\u00edsticas b\u00e1sicas para las publicaciones en 2016 y tambi\u00e9n, aprenderemos las 10 publicaciones principales ordenadas por votaciones.<\/p>\n

N\u00famero total de publicaciones<\/h2>\n

Basado en este 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
Sabemos que hay 245 posts publicados en 2016.<\/p>\n
+----------+\n| count(1) |\n+----------+\n| \u00a0 \u00a0 \u00a0245 |\n+----------+\n1 row in set (0.01 sec)<\/code><\/pre>\n
Principales publicaciones m\u00e1s discutidas<\/h2>\n
Y bas\u00e1ndonos en el SQL<\/a>, tenemos las publicaciones m\u00e1s discutidas para este a\u00f1o, es decir, 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
Conocemos las 10 publicaciones m\u00e1s discutidas (como las m\u00e1s populares) en 2016. Cambie el l\u00edmite de 10<\/strong> para permitir que se muestren m\u00e1s publicaciones, por ejemplo, el l\u00edmite de 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
Estos son (al momento de escribir):<\/p>\n
    \n
  1. Ejercicio de codificaci\u00f3n C\/C++: \u00bfconvertir un n\u00famero a hexadecimal?<\/a><\/li>\n
  2. Programaci\u00f3n Din\u00e1mica \u2013 \u00bfCu\u00e1ntas maneras de conectar las tuber\u00edas?<\/a><\/li>\n
  3. Cuando x==x se eval\u00faa como FALSO en C\/C++?<\/a><\/li>\n
  4. \u00bfC\u00f3mo calcular la suma de dos enteros sin los operadores m\u00e1s+ y menos-?<\/a><\/li>\n
  5. \u00bfC\u00f3mo encontrar la intersecci\u00f3n de dos matrices en C++?<\/a><\/li>\n
  6. Manipulaci\u00f3n de bits: \u00bfC\u00f3mo establecer todos los bits entre i y j en N igual a M?<\/a><\/li>\n
  7. \u00bfC\u00f3mo comprobar un anagrama v\u00e1lido en C\/C++?<\/a><\/li>\n
  8. Ejercicio de codificaci\u00f3n C++: n\u00famero de 1 bits (revisado)<\/a><\/li>\n
  9. Programaci\u00f3n Din\u00e1mica \u2013 Ruptura de Enteros<\/a><\/li>\n
  10. Ejercicio de codificaci\u00f3n C\/C++: encuentre el n\u00famero duplicado<\/a><\/li>\n<\/ol>\n
    Publicaciones m\u00e1s votadas a trav\u00e9s de SQL<\/h2>\n
    Ahora, podemos combinar las estad\u00edsticas de votaci\u00f3n de GD Star Rating<\/a> Plugin y obtener otras clasificaciones interesantes.<\/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
    Esto da las siguientes 10 publicaciones m\u00e1s votadas del a\u00f1o:<\/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. Cuando x==x se eval\u00faa como FALSO en C\/C++?<\/a><\/li>\n
    2. \u00bfC\u00f3mo encontrar el m\u00e1ximo de dos enteros sin usar el operador de comparaci\u00f3n?<\/a><\/li>\n
    3. \u00bfC\u00f3mo eliminar un nodo de un \u00e1rbol de b\u00fasqueda binario?<\/a><\/li>\n
    4. Ejercicio de codificaci\u00f3n C++: \u00bfC\u00f3mo verificar si un entero grande es divisible por 11?<\/a><\/li>\n
    5. Pregunta de la entrevista de Microsoft: obtenga el \u00e1rea del tri\u00e1ngulo<\/a><\/li>\n
    6. PPAP en C++ y Javascript para principiantes<\/a><\/li>\n
    7. \u00bfC\u00f3mo invertir un \u00e1rbol binario en C\/C++?<\/a><\/li>\n
    8. \u00bfC\u00f3mo invertir bits para enteros sin signo de 32 bits en C\/C++?<\/a><\/li>\n
    9. Ejercicio de codificaci\u00f3n C++: \u00bfc\u00f3mo encontrar el primer n\u00famero que falta?<\/a><\/li>\n
    10. Programaci\u00f3n Din\u00e1mica \u2013 Ruptura de Enteros<\/a><\/li>\n<\/ol>\n
      Por supuesto, puede modificar las dos consultas SQL<\/a> anteriores para que proporcione 12 publicaciones, es decir, una mejor para cada mes. Solo necesita agrupar por mes de esta manera:<\/p>\n
      GROUP BY date_format(`p`.post_date_gmt, \"%M\")<\/code><\/pre>\n
      Y, tambi\u00e9n, podr\u00eda crear una p\u00e1gina PHP<\/a> que brinde las tablas de clasificaci\u00f3n m\u00e1s actualizadas para las publicaciones, pero lo que se muestra aqu\u00ed es est\u00e1tico, es decir, la tabla de clasificaci\u00f3n se genera tal como est\u00e1 hoy.<\/p>\n
      sql<\/p>\n
      Fuente de grabaci\u00f3n:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
      \u00bfC\u00f3mo enumerar las publicaciones m\u00e1s votadas en un a\u00f1o usando 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":[892,831,992,914,800,840,861],"tags":[1172],"class_list":["post-232892","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-codigo","category-guia-para-principiantes","category-marketing-de-contenidos","category-otro","category-php-2","category-tutoriales","category-wordpress-2","tag-affiai-es"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/posts\/232892","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/comments?post=232892"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/posts\/232892\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/media\/224083"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/media?parent=232892"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/categories?post=232892"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/es\/wp-json\/wp\/v2\/tags?post=232892"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}