小贝子编程

GroupArray由两个字段组成，并将第二个字段的值相加



我使用的是ClickHouse，我希望按字段1(关键字(分组数据，并在其他字段中显示uniq URL和每个URL的点击值总和(其他字段(，

原始数据

keyword |   URL                                     |  clicks   | impression|   position|   ctr
------------------------------------------------------------------------------------------------
kw1     |   https://mydomain.dev/url1.html          |   10      |   100     |       2   |   **0,10**
kw1     |   https://mydomain.dev/other-url2.html    |   20      |   434     |       3   |   0,05
kw1     |   https://mydomain.dev/url3.html          |   30      |   345     |       4   |   0,09
kw1     |   https://mydomain.dev/url1.html          |   10      |   40      |       5   |   0,25
kw1     |   https://mydomain.dev/other-url2.html    |   22      |   42      |       6   |   0,52
kw1     |   https://mydomain.dev/other-url2.html    |   23      |   43      |       7   |   0,53
Kw2     |   https://mydomain.dev/other-url-data-a   |   435     |   3243    |       1   |   **0,13**
Kw2     |   https://mydomain.dev/other-url-data-b   |   34      |   43      |       4   |   0,79
Kw2     |   https://mydomain.dev/other-url-data-c   |   23      |   433     |       7   |   0,05
Kw2     |   https://mydomain.dev/other-url-data-b   |   232     |   343     |       13  |   0,68
Kw2     |   https://mydomain.dev/other-url-data-a   |   23      |   232     |       14  |   0,10
Kw2     |   https://mydomain.dev/other-url-data-d   |   3       |   34      |       15  |   0,09
Kw2     |   https://mydomain.dev/other-url-data-d   |   23      |   345     |       16  |   0,07
Kw2     |   https://mydomain.dev/other-url-data-c   |   2       |   43      |       17  |   0,05

预期结果：

  • 从最佳位置获取每千瓦的ctr(粗体(
  • 并对uniqurl进行分组，并按url对点击量和印象进行汇总

res

KW, |   URL  clicks : impressions               Cli:Impr|   otalclicks  | TotalUmp  |average    |   ctr     |best_position_by_kw
--------------------------------------------------------------------------------------------------------------------------------
kw1 |   https://mydomain.dev/url1.html          20:140  |       115     |   1004    |3,62       |   10,00%  |   2
|   https://mydomain.dev/other-url2.html    65:519, |               |           |           |           |          
|   https://mydomain.dev/url3.html          30:345  |               |           |           |           |
-------------------------------------------------------------------------------------------------------------------------------
kw2 |   https //mydomain.dev/other-url-data-a   667:3475|       775     |   4716    |4,43       |   13,41%  |   1           
https //mydomain.dev/other-url-data-b   266 386 |               |           |
https //mydomain.dev/other-url-data-c   25 379  |               |           |
https //mydomain.dev/other-url-data-d   26 476  |               |           |

表格和数据：

主表

CREATE TABLE users_data
(
`fecha` Date,
`consulta` String,
`landing` String,
`clicks` UInt32,
`impresiones` UInt32,
`posicion` Float32,
`ctr` Float32,
`dispositivo` String,
`pais` String
)
ENGINE = MergeTree(fecha, landing, 8192)

文件中的表；Grupo"；字段，我只需要这个表的组(用于对类似的kws进行分组(

SELECT
consulta,
any(Grupo) AS Grupo
FROM
file('kw-2020.csv',
'CSVWithNames',
'fecha Date,  consulta String,  landing String,  clicks UInt32,  impresiones UInt32,  posicion Float32,  ctr Float32,  dispositivo String,  pais String,Grupo String')
GROUP BY
consulta

用户数据_数据：

INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','electricidad presupuestos electricos ejemplos','https://mydomain.dev/directory/ld-domain-de-presupuesto-de-elctricidad.php',0,1,7.0,0.0,'MOBILE','chl');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','modelo de carta de recomendacion laboral peru','https://mydomain.dev/directory/ld-domain-de-carta-de-662.php',0,1,5.0,0.0,'DESKTOP','per');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','carta de ofrecimiento de servicios a una empresa','https://mydomain.dev/directory/ld-domain-carta-para-ofrecer-servicios.php',0,3,1.0,0.0,'DESKTOP','slv');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','ejemplo de estrofa','https://mydomain.dev/directory/ld-ejemplo-de-estrofa.php',0,1,28.0,0.0,'DESKTOP','nld');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','carta de intención de servicios profesionales','https://mydomain.dev/directory/goto-domain-de-carta-de-384.php',0,1,5.0,0.0,'DESKTOP','mex');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','ejemplos de presupuesto base cero','https://mydomain.dev/directory/ld-domain-de-presupuesto-base-cero.php',0,1,92.0,0.0,'DESKTOP','mex');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','como se hace una carta de recomendacion','https://mydomain.dev/directory/ld-domain-de-carta-de-662.php',0,1,77.0,0.0,'DESKTOP','twn');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','carta de oferta de productos a clientes','https://mydomain.dev/directory/ld-domain-carta-para-ofrecer-servicios.php',0,1,1.0,0.0,'DESKTOP','esp');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','investigación hemerográfica','https://mydomain.dev/directory/ld-ejemplo-de-ficha-hemerogrfica.php',0,1,44.0,0.0,'DESKTOP','rus');
INSERT INTO  table_name  (fecha,consulta,landing,clicks,impresiones,posicion,ctr,dispositivo,pais) VALUES ('2020-06-22','carta de intención de servicios profesionales','https://mydomain.dev/directory/goto-domain-de-carta-de-384.php',2,6,2.0,0.0,'DESKTOP','col');

文件中的数据

"grupo"    |  "consulta"
-----------------------------------------------------------
pedido    |  carta de pedido ejemplo
carta     |  carta de ofrecimiento de servicios a una empresa
carta     |  carta de intención de servicios profesionales
pedido    |  carta de pedido
carta     |  carta de oferta de productos a clientes
carta     |  carta de intención de servicios profesionales
pedido    |  carta de pedido ejemplo
carta     |  carta de presentacion a una empresa ofreciendo servicios
otros     |  rescisión de contrato modelo
pedido    |  carta de pedidos ejemplos
carta     |  carta de presentacion de empresas de servicios
carta     |  carta solicitando beca universitaria
pedido    |  carta comercial de pedido
carta     |  carta a clientes para ofrecer servicios
otros     |  casilla 99 modelo 390
pedido    |  carta de pedido
carta     |  carta de oferta de servicios
carta     |  carta de intencion de renta
pedido    |  carta de pedido
carta     |  carta de ofrecimiento de productos
otros     |  contrato de leasing modelo
pedido    |  carta de pedidos
carta     |  carta de presentacion de empresa de servicios
otros     |  rescisión de contrato modelo
pedido    |  carta de pedidos
carta     |  carta de presentacion de empresa para ofrecer productos
carta     |  modelo de carta de intencion academica
pedido    |  carta de solicitud de pedido
carta     |  carta de presentacion de servicios
otros     |  modelo de solicitud para beca de estudios
pedido    |  carta de solicitud de pedido
carta     |  carta de presentacion de servicios
otros     |  anexo ampliacion contrato de arras
carta     |  carta de solicitud de un producto
carta     |  carta para ofrecer servicios
otros     |  modelo de rescisión de contrato
pedido    |  carta comercial pedido
carta     |  carta a clientes para ofrecer servicios
otros     |  formato de rescisión de contrato de compraventa de inmueble
pedido    |  carta de anulacion de pedido
carta     |  carta a clientes para ofrecer servicios
otros     |  contrato de trabajo para extranjeros en chile
pedido    |  carta de pedido
carta     |  carta de ofrecer servicios
otros     |  modelo de recision de contrato
pedido    |  carta de pedido
carta     |  carta de oferta de servicios
otros     |  formato de rescisión de contrato de prestacion de servicios
pedido    |  carta de pedido
carta"    |  carta de ofrecimiento de servicios

我需要：对于每个"；grupo"；(包含许多千瓦(我需要

所有不同的"；consulta"；每个组的字段，以及每个consulta的字段：在组中-"；CTR"；其中"；posicion"；字段最低-"posicion"；最后日期

  • 点击和每个不同的"；consulta">
  • %小组中每位顾问的印象和点击率

-所有不同的"；着陆"；和sum"；点击"；以及"；印象"；-%每个URL 的印象和点击量的累积

我试过这句话：

SELECT 
grupo,
groupUniqArray(consulta) as KWS,
groupUniqArray(landing) as landings,
topK(1)(mediaPonderada)[1] AS PosicionFrecuente,
anyLast(mediaPonderada) AS UltimaPosicion,
varSamp(imp) AS varianza2,
sqrt(varianza2) AS desviacion2,
stddevSamp(imp) AS stdImp2,
count() AS cnt,
groupArray((consulta, imp, clicks2, round((clicks2 * 100) / imp, 3), mediaPonderada)) AS consulta_imp,
arraySum(x -> (x.2), consulta_imp) AS total_imp,
total_imp / cnt AS media,
arraySum(x -> (x.3), consulta_imp) AS total_clicks,
round((total_clicks * 100) / total_imp, 2) AS ctr,
arrayMap(x -> ((100 / total_imp) * (x.2)), consulta_imp) AS percent_imp,
arrayCumSum(percent_imp) AS acc_percent_imp,
consulta_imp[1] AS total_ctr2,
total_ctr2.4 AS ctr2
FROM 
(
SELECT 
grupo,
consulta,
landing,
quantileDeterministic(0.5)(impresiones, 10) AS detImp,
avg(impresiones) AS medImp,
varSamp(impresiones) AS varMuestraImp,
varPop(impresiones) AS varPobImp,
count() AS cnt,
sum(clicks) AS clicks2,
sum(impresiones) AS imp,
round((clicks2 * 100) / imp, 2) AS ctr,
round((posicion * impresiones) / impresiones, 0) AS mediaPonderada,
arrayMap(2)(x -> round(x, 2), max([round(posicion, 0), clicks, impresiones])) AS arrclicks,
varSamp(impresiones) AS varianza,
avg(impresiones) AS media,
stddevSamp(impresiones) AS stdImp,
stddevPop(impresiones) AS stdPopImp,
sqrt(varianza) AS desviacion,
stdImp / media AS desvMedia
FROM (
SELECT 
fecha,
consulta,
landing,
clicks,
impresiones,
posicion,
ctr,
dispositivo,
pais,
Grupo as grupo
FROM 
(
SELECT *
FROM GSC_https_Modelode_com2 ghmc 
WHERE fecha > '2020-01-01'
) AS t1
LEFT JOIN 
(
SELECT 
consulta,
any(Grupo) AS Grupo
FROM file('kw-2020.csv', 'CSVWithNames', 'fecha Date,  consulta String,  landing String,  clicks UInt32,  impresiones UInt32,  posicion Float32,  ctr Float32,  dispositivo String,  pais String,`Raiz semantica` String,Volumen UInt32,clickstream UInt32,cpc Float32,Grupo String')
GROUP BY consulta
) AS t2 USING (consulta)
ORDER BY fecha ASC
)
WHERE (fecha > '2020-06-01')
GROUP BY 
grupo,
mediaPonderada,
landing,consulta
ORDER BY 
mediaPonderada ASC,
imp DESC
)
GROUP BY grupo

不起作用，网址重复，点击量和印象深刻

kw      |miArr                                                                                                                                                                                                                                                          
--------|---------------------------------------------------------------------------------------------------------------------------------
kw1     |['[https://mydomain.dev/url1.html, 2]','[https://mydomain.dev/url1.html, 1]','[https://mydomain.dev/url1.html#alternativas, 0]','
kw2     |['[https://mydomain.dev/url2.html, 1]','[https://mydomain.dev/url2.html, 0]']                                                                                                  

select keyword, groupArray((URL, cl, imp)), sum(cl), sum(imp) 
from (
from keyword,URL, sum(clicks) cl, sum(impression) imp
group by keyword,URL)
group by keyword

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium