{"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\/pt\/get-a-bigger-picture-with-n1ql-window-functions-and-cte\/","title":{"rendered":"Obtenha uma vis\u00e3o mais ampla com as fun\u00e7\u00f5es de janela e CTE do N1QL"},"content":{"rendered":"

Seja pela necessidade pessoal de entender seu padr\u00e3o de gastos observando as transa\u00e7\u00f5es banc\u00e1rias, seja pela busca de melhorar o desempenho das vendas em sua organiza\u00e7\u00e3o observando as atividades de vendas, os insights reais s\u00f3 s\u00e3o vis\u00edveis com agrega\u00e7\u00e3o e resumo adicionais dos dados de n\u00edvel transacional. O RDBMS tradicional oferece esse recurso por meio do poder expressivo da agrega\u00e7\u00e3o SQL. As fun\u00e7\u00f5es de janela foram adicionadas ao padr\u00e3o ANSI SQL para ampliar ainda mais a capacidade de expressar perguntas mais complexas com a constru\u00e7\u00e3o SQL.<\/p>\n

O Couchbase N1QL agora suporta as fun\u00e7\u00f5es de janela e a express\u00e3o de tabela comum (CTE) em sua vers\u00e3o 6.5. Os desenvolvedores podem ampliar seus aplicativos para atender a casos de uso comercial mais complexos, al\u00e9m de capacitar os analistas de dados a obter as respostas de que precisam sem realizar p\u00f3s-processamento adicional no Excel.<\/p>\n

Neste artigo, vou me concentrar em alguns exemplos de como voc\u00ea pode aproveitar as fun\u00e7\u00f5es do N1QL Window e o CTE para abordar duas quest\u00f5es comerciais muito comuns.<\/p>\n

Dura\u00e7\u00e3o das atividades de vendas pelos clientes<\/h3>\n

Neste primeiro exemplo de um sistema de gerenciamento de atividades de vendas, queremos fornecer um relat\u00f3rio que mostre o tempo que a equipe de vendas gastou trabalhando com seus clientes no per\u00edodo de janeiro a 2019. Dividirei a consulta em duas etapas:<\/p>\n

1) Obtenha uma lista de reuni\u00f5es e a dura\u00e7\u00e3o da reuni\u00e3o que a equipe de vendas realizou com seus clientes. O tempo total gasto para todos os clientes \"total_time_spent\" \u00e9 calculado pela soma da dura\u00e7\u00e3o das reuni\u00f5es com uma cl\u00e1usula OVER () vazia, que realizar\u00e1 a soma para todo o conjunto de resultados. O tempo total gasto por cliente \"account_time_spent\" usa a mesma constru\u00e7\u00e3o, mas com o \"accid\" para a 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) Em seguida, use as duas m\u00e9tricas para obter a porcentagem do tempo total que a equipe gastou com 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
Atividades de vendas m\u00eas a m\u00eas<\/h3>\n
Neste segundo exemplo, a consulta mostra como o n\u00famero de tarefas relacionadas a vendas mudou m\u00eas a m\u00eas no ano de 2018.  A consulta utiliza o recurso N1QL CTE para melhorar a legibilidade da consulta e tamb\u00e9m a fun\u00e7\u00e3o de janela LAG para obter a contagem de tarefas do per\u00edodo 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. O primeiro CTE - \"current_period_task\" - define a consulta para recuperar uma contagem de todas as atividades do tipo Tarefa agrupadas pelo m\u00eas do calend\u00e1rio.<\/li>\n
  2. O segundo CTE - 'last_period_task' l\u00ea o primeiro CTE e tamb\u00e9m aproveita a fun\u00e7\u00e3o de janela LAG para retornar o task_count do m\u00eas anterior. Observe que a cl\u00e1usula ORDER BY \u00e9 essencial para que a fun\u00e7\u00e3o LAG funcione.<\/li>\n
  3. A consulta principal l\u00ea o segundo CTE - \"last_period_task\" e obt\u00e9m o c\u00e1lculo m\u00eas a m\u00eas.<\/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