Comprobar que un campo contiene texto

By admin on Dec 20, 2008 | In Bienvenid@, Trucos avanzado | Send feedback »

Una tarea común en la limpieza de datos es comprobar que un campo de texto contiene datos válidos. Caracteres de control, iniciales o números pueden ser incómodas valores en algunos campos.
Si no son más que comprobar que la celda contiene texto, en contraposición a los números, me parece más fácil de hacer esto en Excel y exportar los datos si es necesario ..
Para los interesados, la fórmula para hacer esto sería algo como:
=IF(ISTEXT(A3),"Good","False") -- en ingles
=SI(ESTEXTO(A3),"exito","falso") -- en español
Sin embargo, tenemos varias opciones de abordar este problema en Mysql
Una interesante forma de hacer esto podría ser para comprobar que las letras contienen una vocal.
Sin embargo, si nos limitamos a usar el función "substring" de la siguiente manera, podríamos estar en torno a un largo tiempo. El siguiente código muestra la forma en que se iniciaría este:

Code:

SELECT firstname FROM `customer`
  WHERE substring(firstname,1,1) not in ('A','E', 'I', 'O', 'U')
  AND substring(firstname,2,1) not in ('A','E', 'I', 'O', 'U')
  AND substring(firstname,3,1) not in ('A','E', 'I', 'O', 'U')


Una forma mejor sería utilizar la función LOCATE para ver si una palabra contiene una vocal. Si la cadena no se encuentra una vocal entonces LOCATE devuelve cero.

Code:

SELECT firstname FROM `customer`
  WHERE locate('A', firstname) = 0
  and locate('E',firstname) = 0
  and locate('I',firstname) = 0
  and locate('O',firstname) = 0
  and locate('U',firstname) = 0

Aunque he usado LOCATE yo podría fácilmente haber utilizado la función INSTR en su lugar.
Recuerda, sin embargo, que la sintaxis es diferente para esto:
instr(str, substr)
[ INSTR retorna la posición de la primera ocurrencia de la subcadena substr en la cadena str Es lo mismo que la forma de dos argumentos de LOCATE(), excepto que el orden de los argumentos es inverso. ]

Sin embargo, de otra manera sería usar NOT LIKE.

Code:

SELECT firstname FROM `customer`
  WHERE firstname not like '%A%'
  AND firstname not like '%E%'
  AND firstname not like '%I%'
  AND firstname not like '%O%'
  AND firstname not like '%U%'

Algunos de vosotros os habeis dado cuenta de hay fallas en el uso de las anteriores. ¿Qué hay de caso? Tal vez algunos sólo tienen las entradas iniciales en mayusculas? En el campo de texto no puede haber siglas o incluso la palabra inglesa "rhythm" (no contiene vocales - en castellano no hay palabras sin vocales, con una excepción, la preposición "Y").
Tal vez una mejor forma sería la de asegurarse de que el valor de la letra tiene un buen valor ASCII. Por ejemplo:

Code:

select firstname, ascii(substring(firstname,1,1)), gender
  from customer
  where ascii(substring(firstname,1,1)) not between 65 and 122
  and ascii(substring(firstname,1,1)) > 0

Si los números son válidos aquí, podemos probar para valores entre 48 y 122. Aquí hay enlacea a la tabla de valores ascii.

tablas de ascii

ascii tables faq

Por supuesto, el código anterior sólo funciona en pruebas el primer carácter de la cadena. Con el fin de probar todas las características de la cadena tenemos que iterar a través de una cadena de caracteres. Voy a mostrar cómo hacer esto en mi próxima entrada del blog.

Por último, para los programadores avanzados, hay una gran capacidad en MySQL - la capacidad de utilizar las expresiones regulares. Una vez más, esto necesita una mayor explicación de lo que yo tengo tiempo para de esta entrada. Sin embargo, hay una sección dedicada a esto en el manual de Mysql páginas. Aqui es el enlace:
mysql manual regexp

Se puede aprender mas sobre expressiones regulares aqui:

mas sobre expressiones regulares

Si decides utilizar expresiones regulares, recuerda que a pesar de que puede ser muy poderoso, también pueden golpear problemas de rendimiento en tablas grandes.

Bueno, en conclusión, hay muchas maneras de enfocar este tipo de tarea en MySQL. Espero que toma nota de lo anterior han dado una buena idea de cómo podemos hacer esto.

Tags: análisis de los datos, excel, expresiones regulares, instr, locate, regexp

Consultas con GROUP BY

By admin on Nov 3, 2008 | In Aprendiz | Send feedback »

¡Hola, no estoy hibernando todavía!¡He estado muy ocupado con el SqlServer en mi trabajo. Una cláusula útil que utilizo allí es "TOP" para limitar las filas que obtiene tu consulta. Por supuesto tenemos un equivalente en Mysql, es decir LIMIT. Esto es muy útil para establecer un orden en las filas de tu consulta; a proposito, además de restringir la pregunta a las primeras filas, puedes utilizar el OFFSET para saltar filas. Por ejemplo si quiero traer detrás 5 filas de la tabla EMP, pero no los 5 valores más bajos, podría utilizar la declaración siguiente

Code:

select sal from emp order by sal limit 5 offset 5;
+------+
| sal  |
+------+
| 1250 |
| 1300 |
| 1500 |
| 1600 |
| 2450 |
+------+
5 rows in set (0.01 sec)

Ahora otro punto útil sobre la cláusula del LIMIT es se puede utilizarla en consultas de GROUP BY. Claro que tenga sentido, es que la cláusula del LIMIT está trabajando en las filas de registro que se vuelven para la exhibición. Puede ser que los conjuntos de datos grandes produzcan más filas que estás interesado adentro. Usando la cláusula de LIMIT restringirá el número de filas que buscas:

Code:

mysql> select deptno, sum(sal) from emp
    -> group by deptno
    -> order by sum(sal) desc
    -> limit 2;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     20 |    10875 |
|     30 |    10650 |
+--------+----------+
2 rows in set (0.00 sec)

Pienso qué la combinación de GROUP BY con otras funciones y cláusulas, es lo que haga muy poderoso el sql en analizar bases de datos. El código siguiente demuestra esto, pues confía en el uso del operador de la UNION de exhibir diversos resúmenes en el mismo informe. (recuerde utilizar el mismo número de columnas y de mismo tipo de datos en la columna adentro a cada uno la declaración selecta):

Code:

mysql> select deptno, job,
    -> 'TOTAL BY DEPT AND JOB' as category,
    -> sum(sal) as sal
    -> from emp
    -> group by deptno, job
    -> UNION ALL
    -> select deptno, null,
    -> 'TOTAL BY DEPT', sum(sal)
    -> from emp
    -> group by deptno
    -> UNION ALL
    -> select null, null,
    -> 'GRAND TOTAL FOR TABLE', sum(sal)
    -> from emp;
+--------+-----------+-----------------------+-------+
| deptno | job       | category              | sal   |
+--------+-----------+-----------------------+-------+
|     10 | CLERK     | TOTAL BY DEPT AND JOB |  1300 |
|     10 | MANAGER   | TOTAL BY DEPT AND JOB |  2450 |
|     10 | PRESIDENT | TOTAL BY DEPT AND JOB |  5000 |
|     20 | ANALYST   | TOTAL BY DEPT AND JOB |  6000 |
|     20 | CLERK     | TOTAL BY DEPT AND JOB |  1900 |
|     20 | MANAGER   | TOTAL BY DEPT AND JOB |  2975 |
|     30 | CLERK     | TOTAL BY DEPT AND JOB |   950 |
|     30 | MANAGER   | TOTAL BY DEPT AND JOB |  2850 |
|     30 | SALESMAN  | TOTAL BY DEPT AND JOB |  6850 |
|     10 | NULL      | TOTAL BY DEPT         |  8750 |
|     20 | NULL      | TOTAL BY DEPT         | 10875 |
|     30 | NULL      | TOTAL BY DEPT         | 10650 |
|   NULL | NULL      | GRAND TOTAL FOR TABLE | 30275 |
+--------+-----------+-----------------------+-------+
13 rows in set (0.54 sec)

Mola mucho no? (je je !).
Antes de que me vaya, tengo que admitir a conseguir la mayor parte de a mis ejemplos del código este vez del libro SQL CookBook, publicado por O' Reilly. Puede encontrar el libro en este sitio Oreilly Puedo recomendar este libro totalmente.
Bueno, es la hora de irme pero espero que visites mi sitio otra vez pronto. Suerte con el trabajo. ¡Hasta la proxima!

Tags: group by, limit, union

Código hermoso

By admin on Sep 21, 2008 | In Aprendiz, Trucos avanzado | Send feedback »

¡Que me disculpen! - Un aplazamiento desde mi ultima entrada pasada. He comenzado un nuevo trabajo que utiliza SQLServer (¡que pena!), así que mi uso de Mysql ha sido muy pequeño últimamente.
Bueno, tomemos a un poco la mirada en la cláusula Group By. Una de las maneras mas comunes que lo utilizo es traer detrás los registros que se duplican más de una vez. Por ejemplo:

Code:

mysql> select ename, job, deptno,count(*)
from emp
group by ename, job, deptno
having count(*) > 1
 
;
+-------+----------+--------+----------+
| ename | job | deptno | count(*) |
+-------+----------+--------+----------+
| WARD | SALESMAN | 30 | 2 |
+-------+----------+--------+----------+
1 row in set (0.00 sec)

¿Ahora cómo obtendras (suprimir o actualización) el último parte insertado de este registro duplicado? En este caso hay 2 filas, uno con el empno de 7521, el último fila tiene el empno 8000. El código siguiente (en mi opinión humilde) es muy elegante, y es quizás mi pedacito preferido del sql que he visto...gracias a este libro . libro mysql

Code:

mysql> select * from emp
      -> where empno not in (select min(empno)
      ->                     from emp
      ->                     group by ename, job, deptno);
 
+-------+-------+----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+------+------+--------+
| 8000 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
+-------+-------+----------+------+------------+------+------+--------+
1 row in set (0.01 sec)

Aquí está otra manera de usar la cláusula del Group By (otra vez usando subconsultas) para traer detrás a los empleados con el sueldo más alto y más bajo del grupo de datos.

Code:

mysql> select ename, job, deptno
      -> from emp
      -> where sal in ((select min(sal) from emp),
      ->               (select max(sal) from emp));
 
+-------+-----------+--------+
| ename | job | deptno |
+-------+-----------+--------+
| SMITH | CLERK | 20 |
| KING | PRESIDENT | 10 |
+-------+-----------+--------+
2 rows in set (0.10 sec)

Ahora que, a mí está también el código hermoso. ¡Disfruta de tu codificación - hasta la próxima!

Tags: duplicates, group by
1 2 3 >>