{"id":6502,"date":"2019-03-12T09:31:53","date_gmt":"2019-03-12T16:31:53","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=6502"},"modified":"2025-06-13T17:22:10","modified_gmt":"2025-06-14T00:22:10","slug":"get-a-bigger-picture-with-n1ql-window-functions-and-cte","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/es\/get-a-bigger-picture-with-n1ql-window-functions-and-cte\/","title":{"rendered":"Obtenga una visi\u00f3n m\u00e1s amplia con las funciones de ventana y CTE de N1QL"},"content":{"rendered":"

Tanto si se trata de una necesidad personal de entender su patr\u00f3n de gasto a partir de las transacciones bancarias, como si se trata de mejorar el rendimiento de las ventas en su organizaci\u00f3n mediante el an\u00e1lisis de las actividades de ventas, los conocimientos reales s\u00f3lo son visibles con la agregaci\u00f3n y el resumen adicionales de los datos a nivel transaccional. El RDBMS tradicional proporciona esta capacidad a trav\u00e9s del poder expresivo de la agregaci\u00f3n SQL. Las funciones de ventana se a\u00f1adieron al est\u00e1ndar ANSI SQL para ampliar a\u00fan m\u00e1s la capacidad de expresar preguntas m\u00e1s complejas con el constructo SQL.<\/p>\n

Couchbase N1QL ahora soporta las funciones de ventana y la expresi\u00f3n de tabla com\u00fan (CTE) en su versi\u00f3n 6.5. Los desarrolladores pueden ampliar sus aplicaciones para satisfacer casos de uso empresarial m\u00e1s complejos, adem\u00e1s de permitir a los analistas de datos obtener las respuestas que necesitan sin necesidad de realizar un posprocesamiento adicional en Excel.<\/p>\n

En este art\u00edculo me centrar\u00e9 en un par de ejemplos sobre c\u00f3mo puede aprovechar las funciones de ventana N1QL y CTE para abordar dos preguntas de negocio muy comunes.<\/p>\n

Duraci\u00f3n de las actividades de venta por cliente<\/h3>\n

En este primer ejemplo para un sistema de gesti\u00f3n de la actividad de ventas, queremos proporcionar un informe que muestre la cantidad de tiempo que el equipo de ventas ha pasado trabajando con sus clientes durante enero-2019. Desglosar\u00e9 la consulta en dos pasos:<\/p>\n

1) Obtener una lista de las reuniones y la duraci\u00f3n de las mismas que el equipo de ventas ha realizado con sus clientes. El tiempo total empleado para todos los clientes 'tiempo_total_empleado' se calcula sumando la duraci\u00f3n de las citas con una cl\u00e1usula OVER () vac\u00eda, que realizar\u00e1 la suma para todo el conjunto de resultados. El tiempo total empleado por cliente 'tiempo_cuenta_empleado' utiliza la misma construcci\u00f3n, pero con 'accid' para la cl\u00e1usula PARTITION.<\/p>\n

SELECT  c.name, a.startDate, a.title, a.duration, \r\n        SUM(a.duration) OVER() as total_time_spent,\r\n        SUM(a.duration) OVER(PARTITION BY a.accid) AS account_time_spent\r\nFROM crm a\r\n  INNER JOIN crm c ON a.accid = c.id AND  c.type='account'\r\nWHERE a.type='activity'\r\nAND a.activityType='Appointment'\r\nAND a.startDate BETWEEN '2018-01-01' and '2018-01-31'\r\n<\/pre>\n
account_time_spent\tduration\tname\tstartDate\ttitle\ttotal_time_spent\r\n30\t\"30\"\t\"Smith, Avila and Cisneros\"\t\"2018-01-24 15:00:00\"\t\"Switchable coherent adapter\"\t25770\r\n510\t\"150\"\t\"Riddle Ltd\"\t\"2018-01-05 15:30:00\"\t\"Streamlined intermediate intranet\"\t25770\r\n510\t\"90\"\t\"Riddle Ltd\"\t\"2018-01-28 14:30:00\"\t\"Advanced solution-oriented synergy\"\t25770\r\n510\t\"90\"\t\"Riddle Ltd\"\t\"2018-01-01 15:00:00\"\t\"Ameliorated object-oriented methodology\"\t25770\r\n510\t\"30\"\t\"Riddle Ltd\"\t\"2018-01-10 08:00:00\"\t\"Object-based multi-state firmware\"\t25770\r\n510\t\"150\"\t\"Riddle Ltd\"\t\"2018-01-23 09:00:00\"\t\"Multi-layered systematic software\"\t25770\r\n120\t\"30\"\t\"Foster Inc\"\t\"2018-01-29 09:30:00\"\t\"Public-key bottom-line database\"\t25770\r\n120\t\"30\"\t\"Foster Inc\"\t\"2018-01-09 13:00:00\"\t\"Quality-focused local emulation\"\t25770\r\n120\t\"60\"\t\"Foster Inc\"\t\"2018-01-02 08:00:00\"\t\"Digitized motivating matrix\"\t25770\r\n120\t\"30\"\t\"Williams Ltd\"\t\"2018-01-22 08:30:00\"\t\"Versatile heuristic workforce\"\t25770\r\n120\t\"30\"\t\"Williams Ltd\"\t\"2018-01-24 13:30:00\"\t\"Front-line 4thgeneration help-desk\"\t25770\r\n120\t\"60\"\t\"Williams Ltd\"\t\"2018-01-14 14:30:00\"\t\"Visionary upward-trending success\"\t25770\r\n330\t\"150\"\t\"Reid Ltd\"\t\"2018-01-21 10:30:00\"\t\"Profound logistical archive\"\t25770\r\n330\t\"30\"\t\"Reid Ltd\"\t\"2018-01-13 13:30:00\"\t\"Down-sized coherent access\"\t25770\r\n330\t\"120\"\t\"Reid Ltd\"\t\"2018-01-02 12:00:00\"\t\"Front-line object-oriented moderator\"\t25770\r\n330\t\"30\"\t\"Reid Ltd\"\t\"2018-01-12 09:30:00\"\t\"Programmable reciprocal infrastructure\"\t25770\r\n....<\/pre>\n
2) A continuaci\u00f3n, utilice las dos m\u00e9tricas para obtener el porcentaje del tiempo total que el equipo dedic\u00f3 a cada cliente.<\/p>\n
SELECT  c.name,  \r\n  ROUND(( SUM(SUM(a.duration)) OVER(PARTITION BY a.accid)  \/ \r\n   SUM(SUM(a.duration)) OVER()),2) as pct_time\r\nFROM crm a\r\nINNER JOIN crm c ON a.accid = c.id AND  c.type='account'\r\nWHERE a.type='activity'\r\n  AND a.activityType='Appointment'\r\n  AND a.startDate BETWEEN '2018-01-01' and '2018-01-31'\r\nGROUP BY c.name, a.accid\r\nORDER BY 2 DESC<\/pre>\n
name\tpct_time\r\n\"Johnson, Adams and Kelly\"\t0.17\r\n\"Davis Group\"\t0.08\r\n\"Gilbert-Morris\"\t0.08\r\n\"Torres and Sons\"\t0.07\r\n\"Reid Ltd\"\t0.07\r\n\"Medina-Daniels\"\t0.07\r\n\"Riddle Ltd\"\t0.05\r\n\"Henderson and Sons\"\t0.05\r\n\"Gill and Sons\"\t0.05\r\n\"Garcia-Young\"\t0.05\r\n\"Sullivan PLC\"\t0.03\r\n\"Brown-Rogers\"\t0.03\r\n\"Foster Inc\"\t0.03\r\n\"Wheeler Inc\"\t0.03\r\n\"Jarvis-Small\"\t0.03\r\n\"Jones-Fox\"\t0.03\r\n\"Lloyd, Blair and Pruitt\"\t0.03\r\n\"Vaughn LLC\"\t0.02\r\n<\/pre>\n
Actividades de venta mes a mes<\/h3>\n
En este segundo ejemplo, la consulta muestra c\u00f3mo ha cambiado el n\u00famero de tareas relacionadas con las ventas mes a mes para el a\u00f1o 2018.  La consulta aprovecha la funci\u00f3n CTE de N1QL para mejorar la legibilidad de la consulta, y tambi\u00e9n la funci\u00f3n de ventana LAG para obtener el recuento de tareas del periodo anterior.<\/p>\n
WITH current_period_task AS (\r\n  SELECT DATE_TRUNC_STR(a.startDate,'month') AS month,\r\n         COUNT(1) AS current_period_task_count\r\n  FROM crm a \r\n  WHERE a.type='activity' AND a.activityType = 'Task'\r\n    AND   DATE_PART_STR(a.startDate,'year') = 2018\r\n    GROUP BY DATE_TRUNC_STR(a.startDate,'month')\r\n), \r\nlast_period_task AS ( \r\n   SELECT x.month, x.current_period_task_count, \r\n          LAG(x.current_period_task_count) OVER ( ORDER BY x.month) AS last_period_task_count \r\n   FROM current_period_task x\r\n)\r\nSELECT b.month, \r\n       b.current_period_task_count, \r\n       ROUND(((b.current_period_task_count - b.last_period_task_count ) \/ b.last_period_task_count),2) AS MoMChg\r\nFROM last_period_task AS b<\/pre>\n
    \n
  1. El primer CTE - 'current_period_task' define la consulta para recuperar un recuento de todas las actividades de tipo Task agrupadas por el mes natural.<\/li>\n
  2. El segundo CTE - 'last_period_task' lee del primer CTE, y tambi\u00e9n aprovecha la funci\u00f3n de ventana LAG para devolver el recuento de tareas del mes anterior. Tenga en cuenta que la cl\u00e1usula ORDER BY es fundamental para que la funci\u00f3n LAG funcione.<\/li>\n
  3. La consulta principal lee del segundo CTE - 'last_period_task' y deriva el c\u00e1lculo mes a mes.<\/li>\n<\/ol>\n
    MoMChg\tcurrent_period_task_count\tmonth\r\n\t283\t\"2018-01-01\"\r\n-0.08\t260\t\"2018-02-01\"\r\n0.1\t287\t\"2018-03-01\"\r\n-0.08\t264\t\"2018-04-01\"\r\n0.11\t292\t\"2018-05-01\"\r\n0\t293\t\"2018-06-01\"\r\n-0.03\t285\t\"2018-07-01\"\r\n0\t285\t\"2018-08-01\"\r\n0\t284\t\"2018-09-01\"\r\n0\t283\t\"2018-10-01\"\r\n-0.05\t268\t\"2018-11-01\"\r\n0.06\t285\t\"2018-12-01\"\r\n<\/pre>\n
     <\/p>\n
    Recursos<\/h3>\n