tables tablas outer inner full mysql sql database select inner-join

tablas - mysql join types



Consulta MySQL para obtener "intersección" de numerosas consultas con límites (10)

Supongamos que tengo una sola tabla mySQL (usuarios) con los siguientes campos:

userid gender region age ethnicity income

Quiero poder devolver el número de registros totales según el número que ingresa un usuario. Además, también proporcionarán criterios adicionales.

En el ejemplo más simple, pueden solicitar 1,000 registros, donde 600 registros deben tener sexo = ''Hombre'' y 400 registros donde el género = ''Mujer''. Eso es lo suficientemente simple de hacer.

Ahora, vaya un paso más allá. Supongamos que ahora quieren especificar Región:

GENDER Male: 600 records Female: 400 records REGION North: 100 records South: 200 records East: 300 records West: 400 records

De nuevo, solo se deben devolver 1000 registros, pero al final, debe haber 600 hombres, 400 mujeres, 100 habitantes del norte, 200 sureños, 300 orientales y 400 occidentales.

Sé que esto no es una sintaxis válida, pero usando el código pseudo-mySQL, con suerte ilustra lo que estoy tratando de hacer:

(SELECT * FROM users WHERE gender = ''Male'' LIMIT 600 UNION SELECT * FROM users WHERE gender = ''Female'' LIMIT 400) INTERSECT (SELECT * FROM users WHERE region = ''North'' LIMIT 100 UNION SELECT * FROM users WHERE region = ''South'' LIMIT 200 UNION SELECT * FROM users WHERE region = ''East'' LIMIT 300 UNION SELECT * FROM users WHERE region = ''West'' LIMIT 400)

Tenga en cuenta que no estoy buscando una consulta de una sola vez. El número total de registros y el número de registros dentro de cada criterio cambiará constantemente en función de los datos ingresados ​​por el usuario. Por lo tanto, estoy tratando de encontrar una solución genérica que pueda reutilizarse una y otra vez, no una solución codificada.

Para hacer las cosas más complicadas, ahora agregue más criterios. También podría haber edad, etnia e ingresos, cada uno con su propio número de registros para cada grupo, código adicional adjunto al anterior:

INTERSECT (SELECT * FROM users WHERE age >= 18 and age <= 24 LIMIT 300 UNION SELECT * FROM users WHERE age >= 25 and age <= 36 LIMIT 200 UNION SELECT * FROM users WHERE age >= 37 and age <= 54 LIMIT 200 UNION SELECT * FROM users WHERE age >= 55 LIMIT 300) INTERSECT etc.

No estoy seguro si esto es posible escribir en una consulta o si esto requiere múltiples declaraciones e iteraciones.

Aplana tus criterios

Puede aplanar sus criterios multidimensionales en un criterio de un solo nivel

Ahora este criterio se puede lograr en una consulta como sigue

(SELECT * FROM users WHERE gender = ''Male'' AND region = ''North'' LIMIT 40) UNION ALL (SELECT * FROM users WHERE gender = ''Male'' AND region = ''South'' LIMIT 80) UNION ALL (SELECT * FROM users WHERE gender = ''Male'' AND region = ''East'' LIMIT 120) UNION ALL (SELECT * FROM users WHERE gender = ''Male'' AND region = ''West'' LIMIT 160) UNION ALL (SELECT * FROM users WHERE gender = ''Female'' AND region = ''North'' LIMIT 60) UNION ALL (SELECT * FROM users WHERE gender = ''Female'' AND region = ''South'' LIMIT 120) UNION ALL (SELECT * FROM users WHERE gender = ''Female'' AND region = ''East'' LIMIT 180) UNION ALL (SELECT * FROM users WHERE gender = ''Female'' AND region = ''West'' LIMIT 240)

Problema

  • No siempre devuelve el resultado correcto. Por ejemplo, si hay menos de 40 usuarios que son hombres y del norte, la consulta devolverá menos de 1,000 registros.

Ajuste sus criterios

Digamos que hay menos de 40 usuarios que son hombres y del norte. Luego, debe ajustar otra cantidad de criterios para cubrir la cantidad faltante de "Hombre" y "Norte". Creo que no es posible hacerlo con SQL desnudo. Esto es un pseudo código que tengo en mente. En aras de la simplificación, creo que solo consultaremos para hombres, mujeres, norte y sur

conditions.add({ gender: ''Male'', region: ''North'', limit: 40 }) conditions.add({ gender: ''Male'', region: ''South'', limit: 80 }) conditions.add({ gender: ''Female'', region: ''North'', limit: 60 }) conditions.add({ gender: ''Female'', region: ''South'', limit: 120 }) foreach(conditions as condition) { temp = getResultFromDatabaseByCondition(condition) conditions.remove(condition) // there is not enough result for this condition, // increase other condition quantity if (temp.length < condition.limit) { adjust(...); } }

Digamos que solo hay 30 hombres del noreste. Entonces tenemos que ajustar +10 hombres y +10 norteños.

To Adjust --------------------------------------------------- Male +10 North +10 Remain Conditions ---------------------------------------------------- { gender: ''Male'', region: ''South'', limit: 80 } { gender: ''Female'', region: ''North'', limit: 60 } { gender: ''Female'', region: ''South'', limit: 120 }

''Hombre'' + ''Sur'' es la primera condición que coincide con la condición de ajuste ''Masculino''. Aumentarlo en +10, y eliminarlo de la lista "permanecer condición". Dado que, aumentamos el Sur, tenemos que disminuirlo en otras condiciones. Así que agrega la condición "Sur" a la lista "A Ajustar"

To Adjust --------------------------------------------------- South -10 North +10 Remain Conditions ---------------------------------------------------- { gender: ''Female'', region: ''North'', limit: 60 } { gender: ''Female'', region: ''South'', limit: 120 } Final Conditions ---------------------------------------------------- { gender: ''Male'', region: ''South'', limit: 90 }

Encuentra la condición que coincida con el ''Sur'' y repite el mismo proceso.

To Adjust --------------------------------------------------- Female +10 North +10 Remain Conditions ---------------------------------------------------- { gender: ''Female'', region: ''North'', limit: 60 } Final Conditions ---------------------------------------------------- { gender: ''Female'', region: ''South'', limit: 110 } { gender: ''Male'', region: ''South'', limit: 90 }

Y finalmente

{ gender: ''Female'', region: ''North'', limit: 70 } { gender: ''Female'', region: ''South'', limit: 110 } { gender: ''Male'', region: ''South'', limit: 90 }

Todavía no he presentado la implementación exacta del ajuste. Es más difícil de lo que esperaba. Lo actualizaré una vez que pueda descubrir cómo implementarlo.

Construiría un mapa de la distribución de la base de datos y lo usaría para implementar la lógica de muestreo. Las bonificaciones incluyen la posibilidad de agregar comentarios demográficos rápidos para el usuario y ninguna carga adicional para el servidor. Por el contrario, necesitaría implementar un mecanismo para mantener la base de datos y el mapa sincronizados.

Podría verse así usando JSON:

{"gender":{ "Male":{ "amount":35600, "region":{ "North":{ "amount":25000, "age":{ "18":{ "amount":2400, "ethnicity":{ ... "income":{ ... } }, "income":{ ... "ethnicity":{ ... } } }, "19":{ ... }, ... "120":{ ... } }, "ethnicity":{ ... }, "income":{ ... } }, "South":{ ... }, ... } "age":{ ... } "ethnicity":{ ... }, "income":{ ... } }, "Female":{ ... } }, "region":{ ... }, "age":{ ... }, "ethnicity":{ ... }, "income":{ ... }}

Entonces el usuario selecciona

total 1000 600 Male 400 Female 100 North 200 South 300 East 400 West 300 <20 years old 300 21-29 years old 400 >=30 years old

Calcule una distribución lineal:

male-north-u20: 1000*0.6*0.1*0.3=18 male-north-21to29: 18 male-north-o29: 24 (keep a track of rounding errors) etc

entonces verificaríamos el mapa:

tmp.male.north.u20=getSumUnder(JSON.gender.Male.region.North.age,20) // == 10 tmp.male.north.f21to29=getSumBetween(JSON.gender.Male.region.North.age,21,29) // == 29 tmp.male.north.o29=getSumOver(JSON.gender.Male.region.north.age,29) // == 200 etc

Marque todo lo que coincida con la distribución lineal como correcto y realice un seguimiento del excedente. Si algo (como male.north.u20) está debajo, primero ajústelo en parent (para asegurarse de que male.north por ejemplo cumpla con los criterios), le falta 8 para u20 y se usa en exceso 8 para f21to29. Después de la primera ejecución, ajuste cada criterio faltante en otras regiones. Entonces, como tmp.male.south.u20+=8;tmp.male.south.f21to29-=8; .

Es bastante tedioso hacerlo bien.

Al final tiene la distribución correcta que se puede usar para construir una consulta SQL trivial.

El problema con su solicitud es que hay una enorme cantidad de opciones que se pueden usar para alcanzar los números propuestos:

Male Female Sum ----------------------------- North: 100 0 100 South: 200 0 200 East: 300 0 300 West: 0 400 400 Sum: 600 400 ----------------------------- North: 99 1 100 South: 200 0 200 East: 300 0 300 West: 1 399 400 Sum: 600 400 ----------------------------- .... ----------------------------- North: 0 100 100 South: 200 0 200 East: 0 300 300 West: 400 0 400 Sum: 600 400

Simplemente combinando Norte, Este y Oeste (con sur siempre masculino: 200) obtendrás 400 posibilidades de cómo lograr los números propuestos. Y se vuelve aún más complicado cuando solo tienes una cantidad limitada de registros por cada " clase " (Hombre / Norte = " clase ").

Es posible que necesite registros de hasta MIN(COUNT(gender), COUNT(location)) para cada celda de la tabla anterior (en el caso de que su contraparte sea cero).

Eso es hasta:

Male Female --------------------- North: 100 100 South: 200 200 East: 300 300 West: 400 400

Por lo tanto, debe contar los registros disponibles de cada par de género / ubicación AVAILABLE(gender, location) .

Encontrar un ajuste particular parece estar cerca de cuadrados semimágicos [1] [2] .

Y hay varias preguntas sobre math.stackexchange.com sobre esto [3] [4] .

Terminé leyendo un artículo sobre cómo construir estos y dudo que sea posible hacer esto con un selecto.

Si tiene suficientes registros y no terminará en una situación como esta:

Male Female --------------------- North: 100 0 South: 200 200 East: 300 0 West: 200 200

Me gustaría ir iterando a través de ubicaciones y agregar el número proporcional de hombres / mujeres en cada paso:

  1. M: 100 (16%); F: 0 (0%)
  2. M: 100 (16%); F: 200 (50%)
  3. M: 400 (66%); F: 200 (50%)
  4. M: 600 (100%); F: 400 (100%)

Pero esto solo le dará resultados aproximados y, después de validarlos, es posible que desee repetir el resultado pocas veces y ajustar los recuentos en cada categoría para que sean " lo suficientemente buenos ".

El problema que describes es un problema de modelado multidimensional. En particular, intenta obtener una muestra estratificada a lo largo de múltiples dimensiones al mismo tiempo. La clave para esto es bajar al nivel más pequeño de granularidad y construir la muestra desde allí.

Además, estoy suponiendo que desea que la muestra sea representativa en todos los niveles. Es decir, no desea que todos los usuarios de "Norte" sean mujeres. O todos los "varones" son de "Oeste", incluso si eso cumple con los criterios finales.

Comience por pensar en términos de un número total de registros, dimensiones y asignaciones a lo largo de cada dimensión. Por ejemplo, para la primera muestra, piense en ello como:

  • 1000 registros
  • 2 dimensiones: género, región
  • división de género: 60%, 40%
  • división de la región: 10%, 20%, 30%, 40%

Luego, desea asignar estos números a cada combinación de género / región. Los números son:

  • Norte, Hombre: 60
  • Norte, Mujer: 40
  • Sur, Masculino: 120
  • Sur, Mujer: 80
  • Oriente, Masculino: 180
  • Este, Mujer: 120
  • Oeste, Masculino: 240
  • Oeste, Mujer: 160

Verás que estos se suman a lo largo de las dimensiones.

El cálculo de los números en cada celda es bastante fácil. Es el producto de los porcentajes multiplicados por el total. Entonces, "Este, Mujer" es 30% * 40% * 1000. . . Voila! El valor es 120.

Aquí está la solución:

  1. Tome la entrada a lo largo de cada dimensión como porcentajes del total. Y asegúrese de que sumen 100% a lo largo de cada dimensión.
  2. Cree una tabla de los porcentajes esperados para cada una de las celdas. Este es el producto de los porcentajes a lo largo de cada dimensión.
  3. Múltiples los porcentajes esperados por el total general.
  4. La consulta final se describe a continuación.

Supongamos que tiene cells una tabla con el recuento esperado y los datos originales ( users ).

select enumerated.* from (select u.*, (@rn := if(@dims = concat_ws('':'', dim1, dim2, dim3), @rn + 1, if(@dims := concat_ws('':'', dim1, dim2, dim3), 1, 1) ) ) as seqnum from users u cross join (select @dims = '''', @rn := '''') vars order by dim1, dim2, dim3, rand() ) enumerated join cells on enumerated.dims = cells.dims where enuemrated.seqnum <= cells.expectedcount;

Tenga en cuenta que este es un boceto de la solución. Tienes que completar los detalles sobre las dimensiones.

Esto funcionará siempre que tenga datos suficientes para todas las celdas.

En la práctica, al realizar este tipo de muestreo estratificado multidimensional, se corre el riesgo de que las celdas estén vacías o muy pequeñas. Cuando esto sucede, a menudo puede arreglar esto con un pase adicional después. Toma lo que puedas de las celdas que sean lo suficientemente grandes. Estos generalmente representan la mayoría de los datos necesarios. Luego agregue registros para cumplir con el recuento final. Los registros que se agregarán son aquellos cuyos valores coinciden con lo que se necesita a lo largo de las dimensiones más necesarias. Sin embargo, esta solución simplemente supone que hay suficientes datos para satisfacer sus criterios.

Esto se puede resolver en dos pasos. Describiré cómo hacerlo para el ejemplo donde el género y la región son las dimensiones. Luego describiré el caso más general. En el primer paso resolvemos un sistema de ecuaciones de 8 variables, luego tomamos la unión disjunta de 8 declaraciones de selección limitadas por las soluciones encontradas en el paso uno. Tenga en cuenta que solo hay 8 posibilidades para cualquier fila. Pueden ser hombres o mujeres y luego la región es una de norte, sur, este u oeste. Ahora deja,

X1 equal the number of rows that are male and from the north, X2 equal the number of rows that are male and from the south, X3 equal the number of rows that are male and from the east, X4 equal then number that are male and from the west X5 equal the number of rows that are female and from the north, X6 equal the number of rows that are female and from the south, X7 equal the number of rows that are female and from the east, X8 equal then number that are female and from the west

Las ecuaciones son:

X1+X2+X3+X4=600 X5+X6+X7+X8=400 X1+X5=100 X2+X6=200 X3+X7=300 X4+X8=400

Ahora resuelve para X1, X2, ... X8 en lo anterior. Hay muchas soluciones (describiré cómo resolverlas en un momento) Aquí hay una solución:

X1=60, X2=120, X3=180,X4=240,X5=40,X6=80,X7=120,X8=160.

Ahora podemos obtener el resultado mediante una simple unión de 8 selecciones:

(select * from user where gender=''m'' and region="north" limit 60) union distinct(select * from user where gender=''m'' and region=''south'' limit 120) union distinct(select * from user where gender=''m'' and region=''east'' limit 180) union distinct(select * from user where gender=''m'' and region=''west'' limit 240) union distinct(select * from user where gender=''f'' and region=''north'' limit 40) union distinct(select * from user where gender=''f'' and region=''south'' limit 80) union distinct(select * from user where gender=''f'' and region=''east'' limit 120) union distinct(select * from user where gender=''f'' and region=''west'' limit 160);

Tenga en cuenta que si no hay 60 filas en la base de datos, satisfaga la primera selección anterior, entonces la solución particular dada no funcionará. Entonces tenemos que agregar otras restricciones, LT:

0<X1 <= (select count(*) from user where from user where gender=''m'' and region="north") 0<X2 <= (select count(*) from user where gender=''m'' and region=''south'') 0<X3 <= (select count(*) from user where gender=''m'' and region=''east'' ) 0<X4 <= (select count(*) from user where gender=''m'' and region=''west'') 0<X5 <= (select count(*) from user where gender=''f'' and region=''north'' ) 0<X6 <= (select count(*) from user where gender=''f'' and region=''south'') 0<X7 <= (select count(*) from user where gender=''f'' and region=''east'' ) 0<X8 <= (select count(*) from user where gender=''f'' and region=''west'');

Ahora vamos a generalizar para este caso, permitiendo cualquier división. Las ecuaciones son E:

X1+X2+X3+X4=n1 X5+X6+X7+X8=n2 X1+X5=m1 X2+X6=m2 X3+X7=m3 X4+X8=m4

Se dan los números n1, n2, m1, m2, m3, m4 y satisfacen n1 + n2 = (m1 + m2 + m3 + m4). Así que hemos reducido el problema a resolver las ecuaciones LT y E anteriores. Este es solo un problema de programación lineal y se puede resolver usando el método simplex u otros métodos. Otra posibilidad es ver esto como un sistema de ecuaciones diofánticas lineales y usar métodos para encontrar soluciones. En cualquier caso, reduje el problema para encontrar la solución a las ecuaciones anteriores. (Dado que las ecuaciones son de una forma especial, puede haber una manera más rápida que usar el método símplex o resolver un sistema de ecuaciones diofánticas lineales). Una vez que resolvemos para Xi, la solución final es:

(select * from user where gender=''m'' and region="north" limit :X1) union distinct(select * from user where gender=''m'' and region=''south'' limit :X2) union distinct(select * from user where gender=''m'' and region=''east'' limit :X3) union distinct(select * from user where gender=''m'' and region=''west'' limit :X4) union distinct(select * from user where gender=''f'' and region=''north'' limit :X5) union distinct(select * from user where gender=''f'' and region=''south'' limit :X6) union distinct(select * from user where gender=''f'' and region=''east'' limit :X7) union distinct(select * from user where gender=''f'' and region=''west'' limit :X8);

Permite denotar una dimensión D con n posibilidades como D: n. Supongamos que tiene dimensiones D1: n1, D2: n2, ... DM: nM. El generaría n1 * n2 * ... nM variables. La cantidad de ecuaciones generadas es n1 + n2 + ... nM. En lugar de definir el método general, tomemos otro caso de 3 dimensiones, 4 dimensiones y 2 dimensiones; Permite llamar a los valores posibles para que D1 sea d11, d12, d13, D2 es d21, d22, d23, d24 y ​​los valores de D3 son d31, d32. Tendremos 24 variables, y las ecuaciones son:

X1 + X2 + ...X8=n11 X9 + X10 + ..X16=n12 X17+X18 + ...X24=n13 X1+X2+X9+x10+x17+x18=n21 X3+X4+X11+x12+x19+x20=n22 X5+X6+X13+x14+x21+x22=n23 X7+X8+X15+x116+x23+x24=n24 X1+X3+X5+...X23=n31 X2+X4+......X24=n32

Dónde

X1 equals number with D1=d11 and D2=d21 and D3=d31 X2 equals number with D1=d11 and D2=d21 and D3 = d31 .... X24 equals number with D1=D13 and D2=d24, and D3=d32.

Agregue las restricciones menos entonces. Luego resuelve para X1, X2, ... X24. Crea las 24 declaraciones selectivas y toma la unión disjunta. Podemos resolverlo de manera similar para cualquier dimensión.

Entonces, en resumen: dadas las dimensiones D1: n1, D2: n2, ... DM: nM podemos resolver el problema de programación lineal correspondiente como se describe arriba para las variables n1 * n2 * ... nM y luego generar una solución tomando la disyuntiva union sobre n1 * n2 * ... nM select statements. Entonces, sí, podemos generar una solución mediante sentencias select, pero primero debemos resolver las ecuaciones y determinar los límites obteniendo recuentos para cada una de las variables n1 * n2 * ... nM.

Aunque la recompensa haya terminado, voy a agregar un poco más para aquellos a los que les interese. Reclamo aquí que he demostrado completamente cómo resolver esto si hay una solución.

Para aclarar mi enfoque. En el caso de 3 dimensiones, digamos que dividimos la edad en una de 3 posibilidades. Entonces usa bien el género y la región como en la pregunta. Hay 24 posibilidades diferentes para cada usuario correspondiente a donde caen en esas categorías. Deje Xi ser el número de cada una de esas posibilidades en el resultado final. Déjame escribir una matriz donde cada fila representa una de cada posibilidad. Cada usuario contribuirá como máximo 1 a m o f, 1 a norte, sur, este u oeste, y 1 a la categoría de edad. Y solo hay 24 posibilidades para el usuario. Vamos a mostrar una matriz: (abc) las 3 edades, (nsew) las regiones y (mf) masculino o femenino: a es menor o igual a 10 años, b tiene entre 11 y 30 años yc tiene entre 31 y 50 años .

abc nsew mf X1 100 1000 10 X2 100 1000 01 X3 100 0100 10 X4 100 0100 01 X5 100 0010 10 X6 100 0010 01 X7 100 0001 10 X8 100 0001 01 X9 010 1000 10 X10 010 1000 01 X11 010 0100 10 X12 010 0100 01 X13 010 0010 10 X14 010 0010 01 X15 010 0001 10 X16 010 0001 01 X17 001 1000 10 X18 001 1000 01 X19 001 0100 10 X20 001 0100 01 X21 001 0010 10 X22 001 0010 01 X23 001 0001 10 X24 001 0001 01

Cada fila representa un usuario donde hay un 1 en la columna si contribuye a un resultado. Por ejemplo, la primera fila muestra 1 para a, 1 para n y 1 para m. Lo que significa que la edad del usuario es menor o igual a 10, es del norte y es un hombre. El Xi representa cuántos de ese tipo de fila se encuentran en el resultado final. Así que digamos que X1 es 10, lo que significa que estamos diciendo que el resultado final tiene 10 resultados, todos del norte, son hombres y son menos o iguales 10. Bien, ahora solo tenemos que sumar cosas. Tenga en cuenta que los primeros 8 X1+X2+X3+X4+X5+X6+X7+X8 son todas las filas cuya edad es menor o igual a 10. Deben sumarse a lo que elegimos para esa categoría. Del mismo modo para los próximos 2 juegos de 8.

Hasta ahora, obtenemos las ecuaciones: (na es el número con menos de 10 años, n la edad entre 10 y 20 años, nc el número cuya edad es menor que 50

X1+X2+X3+X4+X5+X6+X7+X8 = na X9+X10+X11 + .... X16 = nb X17+X18+X19+... X24=nc

Esas son las divisiones de edad. Ahora veamos la región se divide. Solo suma las variables en la columna "n",

X1+X2+X9+X10+X17+X18 = nn X3+X4+X11+X12+X19+20=ns ...

¿Ves cómo estoy obteniendo esas ecuaciones mirando las columnas? Continuar por ew y mf. dando 3 + 4 + 2 ecuaciones en total. Entonces, lo que hice aquí es bastante simple. He razonado que cualquier fila que elijas contribuye con una de las 3 dimensiones y solo hay 24 posibilidades. Luego, deje que Xi sea el número de cada posibilidad y obtendrá las ecuaciones que necesita resolver. Me parece que cualquier método que se te ocurra debe ser una solución a esas ecuaciones. En otras palabras, simplemente reformulé el problema en términos de resolver esas ecuaciones.

Ahora queremos una solución entera porque no podemos tener una fila fraccionaria. Tenga en cuenta que estas son todas las ecuaciones lineales. Pero queremos una solución entera. Aquí hay un enlace a un documento que describe cómo resolver estos: https://www.math.uwaterloo.ca/~wgilbert/Research/GilbertPathria.pdf

Formar la lógica de negocios en SQL nunca es una buena idea, ya que obstaculizará la capacidad de absorber cambios menores.

Mi sugerencia sería hacer esto en un ORM y mantener la lógica empresarial abstraída de SQL.

Por ejemplo, si estabas usando Django :

Su modelo se vería así:

class User(models.Model): GENDER_CHOICES = ( (''M'', ''Male''), (''F'',''Female'') ) gender = models.CharField(max_length=1, choices=GENDER_CHOICES) REGION_CHOICES = ( (''E'', ''East''), (''W'',''West''), (''N'',''North''), (''S'',''South'') ) region = models.CharField(max_length=1, choices=REGION_CHOICES) age = models.IntegerField() ETHNICITY_CHOICES = ( ....... ) ethnicity = models.CharField(max_length=1, choices=ETHNICITY_CHOICES) income = models.FloatField()

Y su función de consulta podría ser algo como esto:

# gender_limits is a dict like {''M'':400, ''F'':600} # region_limits is a dict like {''N'':100, ''E'':200, ''W'':300, ''S'':400} def get_users_by_gender_and_region(gender_limits,region_limits): for gender in gender_limits: gender_queryset = gender_queryset | User.objects.filter(gender=gender)[:gender_limits[gender]] for region in region_limits: region_queryset = region_queryset | User.objects.filter(region=region)[:region_limits[region]] return gender_queryset & region_queryset

La función de consulta se puede abstraer aún más con el conocimiento de todas las consultas que planea admitir, pero esto debería servir como un ejemplo.

Si está utilizando un ORM diferente, la misma idea se puede traducir a eso también, ya que cualquier buen ORM tendría la unión y la abstracción de intersección.

Usaría un lenguaje de programación para generar las sentencias SQL, pero a continuación hay una solución en mySQL puro. Una suposición hecha: siempre hay suficiente hombre / mujer en una región para ajustarse a los números (por ejemplo, ¿qué ocurre si no hay mujeres viviendo en el norte?).

La rutina es el cálculo previo de las cantidades de fila necesarias. El límite no se puede especificar con una variable. Soy más un tipo oráculo donde tenemos funciones analíticas. MySQL also provides this to some extend by allowing variables. So I set the target regions and gender and calculate the breakdown. Then I limit my output using the calculations.

This query shows the counts to proof the concept.

set @male=600; set @female=400; set @north=100; set @south=200; set @east=300; set @west=400; set @north_male=@north*(@male/(@male+@female)); set @south_male=@south*(@male/(@male+@female)); set @east_male =@east *(@male/(@male+@female)); set @west_male =@west *(@male/(@male+@female)); set @north_female=@north*(@female/(@male+@female)); set @south_female=@south*(@female/(@male+@female)); set @east_female =@east *(@female/(@male+@female)); set @west_female =@west *(@female/(@male+@female)); select gender, region, count(*) from ( select * from (select @north_male :=@north_male-1 as row, userid, gender, region from users where gender = ''Male'' and region = ''North'' ) mn where row>=0 union all select * from (select @south_male :=@south_male-1 as row, userid, gender, region from users where gender = ''Male'' and region = ''South'' ) ms where row>=0 union all select * from (select @east_male :=@east_male-1 as row, userid, gender, region from users where gender = ''Male'' and region = ''East'' ) me where row>=0 union all select * from (select @west_male :=@west_male-1 as row, userid, gender, region from users where gender = ''Male'' and region = ''West'' ) mw where row>=0 union all select * from (select @north_female:=@north_female-1 as row, userid, gender, region from users where gender = ''Female'' and region = ''North'' ) fn where row>=0 union all select * from (select @south_female:=@south_female-1 as row, userid, gender, region from users where gender = ''Female'' and region = ''South'' ) fs where row>=0 union all select * from (select @east_female :=@east_female-1 as row, userid, gender, region from users where gender = ''Female'' and region = ''East'' ) fe where row>=0 union all select * from (select @west_female :=@west_female-1 as row, userid, gender, region from users where gender = ''Female'' and region = ''West'' ) fw where row>=0 ) a group by gender, region order by gender, region;

Salida:

Female East 120 Female North 40 Female South 80 Female West 160 Male East 180 Male North 60 Male South 120 Male West 240

Remove the outer part to get the real records:

set @male=600; set @female=400; set @north=100; set @south=200; set @east=300; set @west=400; set @north_male=@north*(@male/(@male+@female)); set @south_male=@south*(@male/(@male+@female)); set @east_male =@east *(@male/(@male+@female)); set @west_male =@west *(@male/(@male+@female)); set @north_female=@north*(@female/(@male+@female)); set @south_female=@south*(@female/(@male+@female)); set @east_female =@east *(@female/(@male+@female)); set @west_female =@west *(@female/(@male+@female)); select * from (select @north_male :=@north_male-1 as row, userid, gender, region from users where gender = ''Male'' and region = ''North'' ) mn where row>=0 union all select * from (select @south_male :=@south_male-1 as row, userid, gender, region from users where gender = ''Male'' and region = ''South'' ) ms where row>=0 union all select * from (select @east_male :=@east_male-1 as row, userid, gender, region from users where gender = ''Male'' and region = ''East'' ) me where row>=0 union all select * from (select @west_male :=@west_male-1 as row, userid, gender, region from users where gender = ''Male'' and region = ''West'' ) mw where row>=0 union all select * from (select @north_female:=@north_female-1 as row, userid, gender, region from users where gender = ''Female'' and region = ''North'' ) fn where row>=0 union all select * from (select @south_female:=@south_female-1 as row, userid, gender, region from users where gender = ''Female'' and region = ''South'' ) fs where row>=0 union all select * from (select @east_female :=@east_female-1 as row, userid, gender, region from users where gender = ''Female'' and region = ''East'' ) fe where row>=0 union all select * from (select @west_female :=@west_female-1 as row, userid, gender, region from users where gender = ''Female'' and region = ''West'' ) fw where row>=0 ;

For testing I have written a procedure which does create 10000 sample records fully random:

use test; drop table if exists users; create table users (userid int not null auto_increment, gender VARCHAR (20), region varchar(20), primary key (userid) ); drop procedure if exists load_users_table; delimiter # create procedure load_users_table() begin declare l_max int unsigned default 10000; declare l_cnt int unsigned default 0; declare l_gender varchar(20); declare l_region varchar(20); declare l_rnd smallint; truncate table users; start transaction; WHILE l_cnt < l_max DO set l_rnd = floor( 0 + (rand()*2) ); if l_rnd = 0 then set l_gender = ''Male''; else set l_gender = ''Female''; end if; set l_rnd=floor(0+(rand()*4)); if l_rnd = 0 then set l_region = ''North''; elseif l_rnd=1 then set l_region = ''South''; elseif l_rnd=2 then set l_region = ''East''; elseif l_rnd=3 then set l_region = ''West''; end if; insert into users (gender, region) values (l_gender, l_region); set l_cnt=l_cnt+1; end while; commit; end # delimiter ; call load_users_table(); select gender, region, count(*) from users group by gender, region order by gender, region;

Hope this all helps you. The bottom line is: Use a UNION ALL and restrict with pre-calculated variables not LIMIT .

I expect you''d want to generate a bunch of queries based on the required filters.

I''ll explain a possible approach, with a full code sample - but note the caveats later on.
I''ll also address the issue where you can''t fulfil the requested sample from a proportional distribution, but you can from an adjusted distribution - and explain how to do that adjustment

The basic algorithm goes like this:

Start with a set of filters {F1, F2, ... Fn} , each which has a group of values, and percentages which should be distributed amongst those values. For example F1 might be gender, with 2 values (F1V1 = Male: 60%, F1V2 = Female: 40%) You''ll also want the total sample size required (call this X ) From this starting point you can then combine all the filters items from each filter to get a single set all of the combined filter items, and the quantities required for each. The code should be able to handle any number of filters, with any number of values (either exact values, or ranges)

EG: suppose 2 filters, F1: gender, {F1V1 = Male: 60%, F1V2 = Female: 40%}, F2: region, {F2V1 = North: 50%, F2V2 = South: 50%} and a total sample required of X = 10 people.
In this sample we''d like 6 of them to be male, 4 of them to be female, 5 to be from the north, and 5 to be from the south.

Then we do

  1. Create an sql stub for each value in F1 - with an associated fraction of the initial percentage (ie
    • WHERE gender = ''Male'' : 0.6,
    • WHERE gender = ''Female'' : 0.4 )
  2. For each item in F2 - create a new sql stub from every item from the step above - with the filter now being both the F1 Value & the F2 Value, and the associated fraction being the product of the 2 fractions. So we now have 2 x 2 = 4 items of
    • WHERE gender = ''Male'' AND region = ''North'' : 0.6 * 0.5 = 0.3,
    • WHERE gender = ''Female'' AND region = ''North'' : 0.4 * 0.5 = 0.2,
    • WHERE gender = ''Male'' AND region = ''South'' : 0.6*0.5 = 0.3,
    • WHERE gender = ''Female'' AND region = ''South'' : 0.4*0.5 = 0.2
  3. Repeat step 2 above for every additional Filter F3 to Fn. (in our example there were only 2 filters, so we are already done)
  4. Calculate the limit for each SQL stub as being [fraction associated with stub] * X = total required sample size (so for our example thats 0.3 * 10 = 3 for Male/North, 0.2 * 10 = 2 for Female/North etc)
  5. Finally for every sql stub - turn it into a complete SQL statement , and add the limit

Code Sample

I''ll provide C# code for this, but it should be easy enough to translate this to other languages. It would be pretty tricky to attempt this in pure dynamic SQL

Note this is untested - and probably full of errors - but its an idea of the approach you could take.

I''ve defined a public method and a public class - which would be the entry point.

// This is an example of a public class you could use to hold one of your filters // For example - if you wanted 60% male / 40% female, you could have an item with // item1 = {Fraction: 0.6, ValueExact: ''Male'', RangeStart: null, RangeEnd: null} // & item2 = {Fraction: 0.4, ValueExact: ''Female'', RangeStart: null, RangeEnd: null} public class FilterItem{ public decimal Fraction {get; set;} public string ValueExact {get; set;} public int? RangeStart {get; set;} public int? RangeEnd {get; set;} } // This is an example of a public method you could call to build your SQL // - passing in a generic list of desired filter // for example the dictionary entry for the above filter would be // {Key: "gender", Value: new List<FilterItem>(){item1, item2}} public string BuildSQL(Dictionary<string, List<FilterItem>> filters, int TotalItems) { // we want to build up a list of SQL stubs that can be unioned together. var sqlStubItems = new List<SqlItem>(); foreach(var entry in filters) { AddFilter(entry.Key, entry.Value, sqlStubItems); } // ok - now just combine all of the sql stubs into one big union. var result = ""; // Id use a stringbuilder for this normally, // but this is probably more cross-language readable. int limitSum = 0; for(int i = 0; i < sqlStubItems.Count; i++) // string.Join() would be more succinct! { var item = sqlStubItems[i]; if (i > 0) { result += " UNION "; } int limit = (int)Math.Round(TotalItems * item.Fraction, 0); limitSum+= limit; if (i == sqlStubItems.Count - 1 && limitSum != TotalItems) { //may need to adjust one of the rounded items to account //for rounding errors making a total that is not the //originally required total limit. limit += (TotalItems - limitSum); } result += item.Sql + " LIMIT " + Convert.ToString(limit); } return result; } // This method expands the number of SQL stubs for every filter that has been added. // each existing filter is split by the number of items in the newly added filter. private void AddFilter(string filterType, List<FilterItem> filterValues, List<SqlItem> SqlItems) { var newItems = new List<SqlItem>(); foreach(var filterItem in filterValues) { string filterAddon; if (filterItem.RangeStart.HasValue && filterItem.RangeEnd.HasValue){ filterAddon = filterType + " >= " + filterItem.RangeStart.ToString() + " AND " + filterType + " <= " + filterItem.RangeEnd.ToString(); } else { filterAddon = filterType + " = ''" + filterItem.ValueExact.Replace("''","''''") + "''"; //beware of SQL injection. (hence the .Replace() above) } if(SqlItems.Count() == 0) { newItems.Add(new SqlItem(){Sql = "Select * FROM users WHERE " + filterAddon, Fraction = filterItem.Fraction}); } else { foreach(var existingItem in SqlItems) { newItems.Add(new SqlItem() { Sql = existingItem + " AND " + filterAddon, Fraction = existingItem.Fraction * filterItem.Fraction }); } } } SqlItems.Clear(); SqlItems.AddRange(newItems); } // this class is for part-built SQL strings, with the fraction private class SqlItem{ public string Sql { get; set;} public decimal Fraction{get; set;} }

Notes (as per comment by Sign)

  • Rounding errors may mean you don''t get exactly the 600 / 400 split you were aiming for when applying a large number of filters - but should be close.
  • If your dataset is not very diverse then it may not be possible to always generate the required split. This method will require an even distribution amongst the filters (so if you were doing a total of 10 people, 6 male, 4 female , 5 from the north, 5 from the south it would require 3 males from the north, 3 males from the south, 2 females from the north and 2 females from the south.)
  • The people are not going to be retrieved at random - just whatever the default sort is. You would need to add something like ORDER BY RAND() (but not that as its VERY inefficient) to get a random selection.
  • Beware of SQL injection. Sanitise all user input, replacing single quote '' chars.

Badly distributed sample problem

How do you address the problem of there being insufficient items in one of our buckets to create our sample as per a representative split (that the above algorithm gives)? Or what if your numbers are not integers?

Well I won''t go so far as to provide code, but I will describe a possible approach. You''d need to alter the code above quite a bit, because a flat list of sql stubs isn''t going to cut it anymore. Instead you''d need to build a n-dimensional matrix of SQL stubs (adding a dimension for every filter F1 - n) After step 4 above has been completed (where we have our desired, but not necessarily possible numbers for each SQL stub item), what I''d expect to do is

  1. generate SQL to select counts for all the combined sql WHERE stubs.
  2. Then you''d iterate the collection - and if you hit an item where the requested limit is higher than the count (or not an integer),
    • adjust the requested limit down to the count (or nearest integer).
    • Then pick another item on each of the axis that is at least the above adjustment lower that its max count, and adjust it up by the same. If its not possible to find qualifying items then your requested split is not possible.
    • Then adjust all the intersecting items for the upward adjusted items down again
    • Repeat the step above for intersects between the intersecting points for every additional dimension to n (but toggle the adjustment between negative and positive each time)

So suppose continuing our previous example - our representative split is:
Male/North = 3, Female/North = 2, Male/South = 3, Female/South = 2, but there are only 2 Males in the north (but theres loads of people in the other groups we could pick)

  • We adjust Male/North down to 2 (-1)
  • We adjust Female/North to 3 (+1) and Male/South to 4 (+1)
  • We adjust the Intersecting Female/South to 1 (-1). Voila! (there are no additional dimensions as we only had 2 criteria/dimensions)

This illustration may be helpful when adjusting intersecting items in higher dimensions (only showing up to 4 dimensions, but should help to picture what needs to be done! Each point represents one of our SQL stub items in the n-dimensional matrix (and has an associated limit number) A line represents a common criteria value (such as gender = male). The objective is that the total along any line should remain the same after adjustments have finished! We start with the red point, and continue for each additional dimension... In the example above we would only be looking at 2 dimensions - a square formed from the red point, the 2 orange points above and to the right of it, and the 1 green point to the NE to complete the square.

I''d go with GROUP BY :

SELECT gender,region,count(*) FROM users GROUP BY gender,region

+----------------------+ |gender|region|count(*)| +----------------------+ |f |E | 129| |f |N | 43| |f |S | 84| |f |W | 144| |m |E | 171| |m |N | 57| |m |S | 116| |m |W | 256| +----------------------+

You can verify you have 600 males, 400 females, 100 North, 200 South, 300 East and 400 West.

You can include other fields as well.

Para campos de rango, como edad e ingresos, puede seguir este ejemplo:

SELECT gender, region, case when age < 30 then ''Young'' when age between 30 and 59 then ''Middle aged'' else ''Old'' end as age_range, count(*) FROM users GROUP BY gender,region, age_range

Entonces, los resultados serían como:

+----------------------------------+ |gender|region|age |count(*)| +----------------------------------+ |f |E |Middle aged| 56| |f |E |Old | 31| |f |E |Young | 42| |f |N |Middle aged| 14| |f |N |Old | 11| |f |N |Young | 18| |f |S |Middle aged| 40| |f |S |Old | 23| |f |S |Young | 21| |f |W |Middle aged| 67| |f |W |Old | 42| |f |W |Young | 35| |m |E |Middle aged| 77| |m |E |Old | 56| |m |E |Young | 38| |m |N |Middle aged| 13| |m |N |Old | 25| |m |N |Young | 19| |m |S |Middle aged| 46| |m |S |Old | 39| |m |S |Young | 31| |m |W |Middle aged| 103| |m |W |Old | 66| |m |W |Young | 87| +----------------------------------+

Well, I think the question is about randomly getting the records and not in the proportion of 60/40 for all regions. I have done for Region and Gender. It can be generalized to other fields like age, income and ethnicity in the same way.

Declare @Mlimit bigint Declare @Flimit bigint Declare @Northlimit bigint Declare @Southlimit bigint Declare @Eastlimit bigint Declare @Westlimit bigint Set @Mlimit= 600 Set @Flimit=400 Set @Northlimit= 100 Set @Southlimit=200 Set @Eastlimit=300 Set @Westlimit=400 CREATE TABLE #Users( [UserId] [int] NOT NULL, [gender] [varchar](10) NULL, [region] [varchar](10) NULL, [age] [int] NULL, [ethnicity] [varchar](50) NULL, [income] [bigint] NULL ) Declare @MnorthCnt bigint Declare @MsouthCnt bigint Declare @MeastCnt bigint Declare @MwestCnt bigint Declare @FnorthCnt bigint Declare @FsouthCnt bigint Declare @FeastCnt bigint Declare @FwestCnt bigint Select @MnorthCnt=COUNT(*) from users where gender=''male'' and region=''north'' Select @FnorthCnt=COUNT(*) from users where gender=''female'' and region=''north'' Select @MsouthCnt=COUNT(*) from users where gender=''male'' and region=''south'' Select @FsouthCnt=COUNT(*) from users where gender=''female'' and region=''south'' Select @MeastCnt=COUNT(*) from users where gender=''male'' and region=''east'' Select @FeastCnt=COUNT(*) from users where gender=''female'' and region=''east'' Select @MwestCnt=COUNT(*) from users where gender=''male'' and region=''west'' Select @FwestCnt=COUNT(*) from users where gender=''female'' and region=''west'' If (@Northlimit=@MnorthCnt+@FnorthCnt) begin Insert into #Users select * from Users where region=''north'' set @Northlimit=0 set @Mlimit-=@MnorthCnt set @Flimit-=@FnorthCnt set @MnorthCnt=0 set @FnorthCnt=0 end If (@Southlimit=@MSouthCnt+@FSouthCnt) begin Insert into #Users select * from Users where region=''South'' set @Southlimit=0 set @Mlimit-=@MSouthCnt set @Flimit-=@FSouthCnt set @MsouthCnt=0 set @FsouthCnt=0 end If (@Eastlimit=@MEastCnt+@FEastCnt) begin Insert into #Users select * from Users where region=''East'' set @Eastlimit=0 set @Mlimit-=@MEastCnt set @Flimit-=@FEastCnt set @MeastCnt=0 set @FeastCnt=0 end If (@Westlimit=@MWestCnt+@FWestCnt) begin Insert into #Users select * from Users where region=''West'' set @Westlimit=0 set @Mlimit-=@MWestCnt set @Flimit-=@FWestCnt set @MwestCnt=0 set @FwestCnt=0 end If @MnorthCnt<@Northlimit Begin insert into #Users select top (@Northlimit-@MnorthCnt) * from Users where gender=''female'' and region=''north'' and userid not in (select userid from #users) set @Flimit-=(@Northlimit-@MnorthCnt) set @FNorthCnt-=(@Northlimit-@MnorthCnt) set @Northlimit-=(@Northlimit-@MnorthCnt) End If @FnorthCnt<@Northlimit Begin insert into #Users select top (@Northlimit-@FnorthCnt) * from Users where gender=''male'' and region=''north'' and userid not in (select userid from #users) set @Mlimit-=(@Northlimit-@FnorthCnt) set @MNorthCnt-=(@Northlimit-@FnorthCnt) set @Northlimit-=(@Northlimit-@FnorthCnt) End if @MsouthCnt<@southlimit Begin insert into #Users select top (@southlimit-@MsouthCnt) * from Users where gender=''female'' and region=''south'' and userid not in (select userid from #users) set @Flimit-=(@southlimit-@MsouthCnt) set @FSouthCnt-=(@southlimit-@MsouthCnt) set @southlimit-=(@southlimit-@MsouthCnt) End if @FsouthCnt<@southlimit Begin insert into #Users select top (@southlimit-@FsouthCnt) * from Users where gender=''male'' and region=''south'' and userid not in (select userid from #users) set @Mlimit-=(@southlimit-@FsouthCnt) set @MSouthCnt-=(@southlimit-@FsouthCnt) set @southlimit-=(@southlimit-@FsouthCnt) End if @MeastCnt<@eastlimit Begin insert into #Users select top (@eastlimit-@MeastCnt) * from Users where gender=''female'' and region=''east'' and userid not in (select userid from #users) set @Flimit-=(@eastlimit-@MeastCnt) set @FEastCnt-=(@eastlimit-@MeastCnt) set @eastlimit-=(@eastlimit-@MeastCnt) End if @FeastCnt<@eastlimit Begin insert into #Users select top (@eastlimit-@FeastCnt) * from Users where gender=''male'' and region=''east'' and userid not in (select userid from #users) set @Mlimit-=(@eastlimit-@FeastCnt) set @MEastCnt-=(@eastlimit-@FeastCnt) set @eastlimit-=(@eastlimit-@FeastCnt) End if @MwestCnt<@westlimit Begin insert into #Users select top (@westlimit-@MwestCnt) * from Users where gender=''female'' and region=''west'' and userid not in (select userid from #users) set @Flimit-=(@westlimit-@MwestCnt) set @FWestCnt-=(@westlimit-@MwestCnt) set @westlimit-=(@westlimit-@MwestCnt) End if @FwestCnt<@westlimit Begin insert into #Users select top (@westlimit-@FwestCnt) * from Users where gender=''male'' and region=''west'' and userid not in (select userid from #users) set @Mlimit-=(@westlimit-@FwestCnt) set @MWestCnt-=(@westlimit-@FwestCnt) set @westlimit-=(@westlimit-@FwestCnt) End IF (@MnorthCnt>=@Northlimit and @FnorthCnt>=@Northlimit and @MsouthCnt>=@southlimit and @FsouthCnt>=@southlimit and @MeastCnt>=@eastlimit and @FeastCnt>=@eastlimit and @MwestCnt>=@westlimit and @FwestCnt>=@westlimit and not(@Mlimit=0 and @Flimit=0)) Begin ---Create Cursor DECLARE UC CURSOR FAST_forward FOR SELECT * FROM Users where userid not in (select userid from #users) Declare @UserId [int] , @gender [varchar](10) , @region [varchar](10) , @age [int] , @ethnicity [varchar](50) , @income [bigint] OPEN UC FETCH NEXT FROM UC INTO @UserId ,@gender, @region, @age, @ethnicity, @income WHILE @@FETCH_STATUS = 0 and not (@Mlimit=0 and @Flimit=0) BEGIN If @gender=''male'' and @region=''north'' and @Northlimit>0 AND @Mlimit>0 begin insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income) set @Mlimit-=1 set @MNorthCnt-=1 set @Northlimit-=1 end If @gender=''male'' and @region=''south'' and @southlimit>0 AND @Mlimit>0 begin insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income) set @Mlimit-=1 set @MsouthCnt-=1 set @Southlimit-=1 end If @gender=''male'' and @region=''east'' and @eastlimit>0 AND @Mlimit>0 begin insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income) set @Mlimit-=1 set @MeastCnt-=1 set @eastlimit-=1 end If @gender=''male'' and @region=''west'' and @westlimit>0 AND @Mlimit>0 begin insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income) set @Mlimit-=1 set @MwestCnt-=1 set @westlimit-=1 end If @gender=''female'' and @region=''north'' and @Northlimit>0 AND @flimit>0 begin insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income) set @Flimit-=1 set @FNorthCnt-=1 set @Northlimit-=1 end If @gender=''female'' and @region=''south'' and @southlimit>0 AND @flimit>0 begin insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income) set @Flimit-=1 set @FsouthCnt-=1 set @Southlimit-=1 end If @gender=''female'' and @region=''east'' and @eastlimit>0 AND @flimit>0 begin insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income) set @flimit-=1 set @feastCnt-=1 set @eastlimit-=1 end If @gender=''female'' and @region=''west'' and @westlimit>0 AND @flimit>0 begin insert into #Users values (@UserId ,@gender, @region, @age, @ethnicity, @income) set @flimit-=1 set @fwestCnt-=1 set @westlimit-=1 end FETCH NEXT FROM UC INTO @UserId ,@gender, @region, @age, @ethnicity, @income END CLOSE UC DEALLOCATE UC end Select * from #Users SELECT GENDER, REGION, COUNT(*) AS COUNT FROM #USERS GROUP BY GENDER, REGION DROP TABLE #Users