mostrar ultimos registros teniendo en cuenta dos campos en común

[pithon]

Hola,no encuentro la lógica para recuperar registros sin tener que hacer varias consultas, intentaré explicar lo que quiero lograr, haber si me podeis ayudar.


quiero recuperar los últimos 50 registros de una tabla siempre y cuando cumplan varias opciones:

-registros que sean a partir de los 100 primeros.

-este recuento se debe hacer con los registros que tengan dos campos iguales.

-estos campos no importa su valor solo que deben ser iguales, debido a que existen bastantes valores diferentes.


es decir los registros tienen que ser a partir de los 100 primeros, pero este recuento se debe hacer teniendo en cuenta los que tienen dos campos iguales, es aquí mi confusión para lograrlo.

[pithon]

Nadie me puede ayudar, formulare la pregunta de otra manera para que se entienda mejor.
quiero mostrar los ultimos 50 clientes de cada pais y ciudad empezando desde el registro 100.

Código MySQL:

select * from clientes where pais='mexico' and ciudad='cancun' order by id desc limit 100,50;
 

Esa seria la consulta basica para un pais y ciudad en concreto, el problema está en que en la base de datos hay muchos paises y ciudades.

saludos

[Libras]

Iniciado por pithon

Nadie me puede ayudar

Es obligacion ayudarte? Primero aprende a poner bien definida tu pregunta, y con ejemplos para que se entienda mucho mejor, en sql server podrias usar un rownumber con partition basado en el pais y la ciudad, aqui hay un ejemplo de que podrias usar, si te fijas lo hace por una sola columna en tu caso tendrias que usar 2

Ejemplo aqui

[Libras]

Naide puede responder si le funciono la solucion o no?

[pithon]

Iniciado por Libras

Naide puede responder si le funciono la solucion o no?

Hola, perdona no quise decir que es obligatorio ayudar, esta claro que ayuda si se puede y se quiere.
Volviendo al tema, no entiendo muy bien cómo aplicar row_number para crear mi consulta.
partiendo de esta consulta:

Código MySQL:

select * from clientes where pais='mexico' and ciudad='cancun' order by id desc limit 100,50;
 

me muestra los 50 registros a partir del 100, yo necesito mostrar en total 50 registros teniendo en cuenta todos los países, por ejemplo:

Código MySQL:

select * from clientes where pais='mexico' and ciudad='cancun'  limit 100,50;
 

Código MySQL:

select * from clientes where pais='colombia' and ciudad='bogota' limit 100,50 ;
 

así sucesivamente con cada país y su ciudad, aunque estas consultas están devolviendo 50 registros por cada país y como digo necesito en total 50 registros de todos los países, por orden de fecha, de id o lo que sea pero solo 50.

De todas formas estoy intentando crear la consulta, pero estoy bloqueado, cualquier ayuda se agradece.

[Libras]

Con el row_number con partition te crea registros ordenados de acuerdo a un criterio(en tu caso pais y ciudad) digamos tienes esto

Mexico cancun
Mexico cancun
Mexico cancun
Mexico cancun
Mexico Gdl
Mexico Gdl
Colombia Bogota
Colombia Bogota
Colombia Bogota

El row number con partition te regresaria lo siguiente

Mexico cancun 1
Mexico cancun 2
Mexico cancun 3
Mexico cancun 4
Mexico Gdl 1
Mexico Gdl 2
Colombia Bogota 1
Colombia Bogota 2
Colombia Bogota 3

Con eso podrias hacer un query que solo te regrese donde los valores del rownumber sean menores a 50(los primeros 50)

Se entiende?

[pithon]

Hola, el rownumber lo entiendo pero no acabo de entender cómo plantear la consulta, en cada país y ciudad quiero registros que sean a partir de los 100 primeros, es decir si la consulta del pais mexico y ciudad cancun, diera 99 registros no quiero ninguno, dicho esto aparte no quiero 50 registros de consulta de país y ciudad, quiero 50 registros en total. Este total de 50 seria los que tengan la fecha más alta.

Dicho esto, creo que tendria que usar dos condicionales uno para devolver registros a partir del registro 100 de cada país y ciudad y otra para devolver los 50 registros del total de resultados con fecha más alta.


Nose si esa es la forma de hacerlo usando rownumber, igual no se como aplicar los condicionales, voy a leer sobre el tema.

Me podrias confirmar si esa es la idea y algún ejemplo de como aplicar condiciones a dicha consulta.

gracias y saludos

[Libras]

Ahi podrias primero meter un count con un having para saber registros regresan 100 elementos algo como esto:

Código SQL:

SELECT * FROM tabla AS t1 
INNER JOIN
(
 
SELECT COUNT(*) AS total, pais, ciudad FROM tabla
GROUP BY pais, ciudad
HAVING COUNT(*)>=100
) AS t2 ON (t1.ciudad=t2.ciudad AND t1.pais=t2.pais)
 

en la primer consulta podrias poner el rownumber para que te regrese los registros que necesites agrupados

[pithon]

Iniciado por Libras

Ahi podrias primero meter un count con un having para saber registros regresan 100 elementos algo como esto:

Código SQL:

SELECT * FROM tabla AS t1 
INNER JOIN
(
 
SELECT COUNT(*) AS total, pais, ciudad FROM tabla
GROUP BY pais, ciudad
HAVING COUNT(*)>=100
) AS t2 ON (t1.ciudad=t2.ciudad AND t1.pais=t2.pais)
 

en la primer consulta podrias poner el rownumber para que te regrese los registros que necesites agrupados

Hola ahora me estoy liando , mañana me lo mirare con calma haber si acabo de entenderlo, porque ahora mismo no entiendo nada.
Gracias por tu ayuda.
saludos

[Libras]

Mira este link

[pithon]

Iniciado por Libras

Mira este link

Ah vale ahora entiendo el HAVING, pero igual ahora estoy saturado para montar la consulta mañana la intentó montar, aunque no se si podré lograrlo .
Gracias y saludos

[pithon]

No acabo de aplicar bien el rownumber para hacer funcionar mi consulta, esto es lo que tengo hasta ahora.

Código MySQL:

SELECT * FROM clientes AS t1 
                INNER JOIN
                  (
                  SELECT COUNT(*) AS total, pais, ciudad FROM clientes
                                  WHERE estado=0 
                  GROUP BY pais, ciudad
                  HAVING COUNT(*)>=100
                  ) AS t2 ON (t1.pais=t2.pais AND t1.ciudad=t2.ciudad) 
                       ORDER BY fecha LIMIT 50
 

En la consulta actual me esta mostrando los registros agrupados por país y ciudad que tengan mas o igual a 100 registros mostrandome un total de 50 ordenados por la fecha, el problema es que los 100 primeros registros de cada agrupación de pais y ciudad no me los deberia mostrar. Es ahi donde entra en juego rownumber, pero no consigo aplicarlo a la consulta.

Sigo investigando, cualquier ayuda se agradece, saludos.

[pithon]

no consigo hacer funcionar la consulta muestro donde me quede bloqueado, esta es la que tengo:

Código MySQL:

SELECT   
                         @row_number:=CASE
                         WHEN @pais = t1.pais AND @ciudad=t1.ciudad THEN @row_number + 1
                         ELSE 1
                         END AS num,
                         @pais:=t1.pais AS P,
                         @ciudad:=t1.ciudad AS C,
                         fecha,
                         nombre,
                         id
                  FROM clientes AS t1 
                  INNER JOIN
                  (
                  SELECT COUNT(*) AS total,pais,ciudad FROM clientes
                  WHERE estado=0
                  GROUP BY pais, ciudad
                  HAVING COUNT(*)>100
                  ) AS t2 ON (t1.pais=t2.pais AND t1.ciudad=t2.ciudad) 
                  ORDER BY date LIMIT 50
 

En primer lugar, no consigo incrementar en 1 la variable row_number, nunca se cumple la condición y el row_number toma el valor de 1, en los resultados se aprecia que los países y sus ciudades estan repetidos y no se porque no se cumple la condición.

Después no se como debo decirle que me recupere solo los registros a partir del 100, sería algo como mostrar registros donde la variable row_number es mayor que 100, aunque no se como aplicarlo en la consulta.

[Libras]

Prueba con esta consulta para ver lo del rownumber:

Código MySQL:

select * from(
                SELECT   
                         CASE
                         WHEN t1.pais = @pais AND t1.ciudad=@ciudad THEN @rownum := @rownum + 1
                         ELSE @rownum := 1
                         END AS num,
                         @pais:= t1.pais AS P,
                         @ciudad:=t1.ciudad AS C,
                         fecha,
                         nombre,
                         id
                  FROM clientes AS t1 
                  ) as t;
 

[pithon]

Iniciado por Libras

Prueba con esta consulta para ver lo del rownumber:

Código MySQL:

select * from(
                SELECT   
                         CASE
                         WHEN t1.pais = @pais AND t1.ciudad=@ciudad THEN @rownum := @rownum + 1
                         ELSE @rownum := 1
                         END AS num,
                         @pais:= t1.pais AS P,
                         @ciudad:=t1.ciudad AS C,
                         fecha,
                         nombre,
                         id
                  FROM clientes AS t1 
                  ) as t;
 

Hola, probe la consulta y tampoco me incrementa el rownum.

[pithon]

Estado haciendo pruebas y el problema lo tengo con el COLLATE utf8_unicode_ci, el case no funciona si el campo no tiene ese COLLATE, como podria agregarlo.

para un campo en concreto sería fácil, algo asi:

case pais COLLATE utf8_unicode_ci.

pero al tener que usar dos campos para agrupar no se como puedo cambiar el COLLATE solo para dicha consulta.

saludos

[pithon]

El rownumber lo hice funcionar pero solo teniendo en cuenta el pais, no se como hacerlo teniendo en cuenta tambien la ciudad.

Código MySQL:

SELECT
    pais,
    (
        CASE pais COLLATE utf8_unicode_ci
        WHEN @curPais THEN
            @curRow := @curRow + 1
        ELSE
            @curRow := 1
        AND @curPais := pais 
        END
    ) AS totalPais
FROM
    clientes t,
    (
        SELECT
            @curRow := 0,
            @curPais := ''
    ) rt
ORDER BY
    pais
 

Tampoco encuentro la manera para que me recupere los registros a partir del 100, la variable que contiene las filas que solo recupere los que son mayores que 100.

[pithon]

Al final conseguí que funcione la consulta pero es un poco lenta.

Código MySQL:

SELECT * FROM (
 
SELECT
    pais,ciudad
    (
        CASE CONCAT(pais COLLATE utf8_unicode_ci,ciudad COLLATE utf8_unicode_ci)
        WHEN @curInput THEN
            @curRow := @curRow + 1
        ELSE
            @curRow := 1
        AND @curInput := CONCAT(pais,ciudad)
        END
    ) AS row,
    fecha AS f
FROM
    clientes c,
    (
        SELECT
            @curRow := 0,
            @curInput := ''
    ) rp
ORDER BY
    pais,ciudad
) AS t1 
 
INNER JOIN
 
(SELECT COUNT(*) AS total,pais,ciudad FROM clientes WHERE estado=1 GROUP BY pais,ciudad HAVING COUNT(*)>=100) AS t2 
 
ON t1.pais=t2.pais AND t1.ciudad=t2.ciudad AND row>50 ORDER BY f  LIMIT 50
 

¿Es posible hacerlo de una manera mas eficiente, logrando el mismo resultado?

Gracias y saludos.

[Libras]

Lenta? que consideras lento? cual es tu num de registros? cual es tu plan de ejecucion? tienes indices?

[pithon]

Iniciado por Libras

Lenta? que consideras lento? cual es tu num de registros? cual es tu plan de ejecucion? tienes indices?

Tengo 1000 registros pero debe trabajar con 200.000 aumentando consideradamente su lentitud, si tengo indices.

En realidad la consulta no se ejecutar muchas veces, pero solo pregunte si es posible hacerla mas eficiente, de lo contrario la dejare asi que tampoco esta tan mal.

Gracias Libras por toda tu ayuda, saludos

[pithon]

Me esta funcionando bien la consulta pero quiero cambiar una cosa y no se como hacerlo, comento por si alguien me quiere ayudar.

las filas las numero teniendo en cuenta el pais y ciudad por ejemplo

pais
mexico
mexico
mexico
colombia
ciudad
cancun
cancun
cancun
cliente
luis
ana
marcelo
maria

quedando asi:

fila
1
2
3
1
pais
mexico
mexico
mexico
colombia
ciudad
cancun
cancun
cancun
cliente
luis
ana
marcelo
maria


como tendria que hacer para ordenar por fechas e incrementar las filas segun su pais y ciudad, quedando algo asi:

fila
3
1
2
1
fecha
29-12-2018
20-12-2018
22-12-2018
20-02-2019
pais
mexico
mexico
mexico
colombia
ciudad
cancun
cancun
cancun
cliente
luis
ana
marcelo
maria


En resumen utilizar la simulacion del rownumber para incrementar el grupo de pais y ciudad pero ordenados por fecha.
saludos