domingo, 18 de enero de 2009

DESAFIO SQL (ORDER BY). LA SOLUCIÓN


En primer lugar, un comentario sobre las 'herramientas' a utilizar.
  1. Para descomponer la cadena en segmentos : http://sqlraipon.blogspot.com/2008/04/descomponer-lista-csv-en-registros.html Se trata de la adaptación a Access de un ejemplo del mismo Itzik Ben-Gan.
  2. Dentro del ejemplo anterior, es necesario generar una secuencia continua de números : http://sqlraipon.blogspot.com/2008/04/series-numricas.html.
  3. Las subconsultas, son para Access un 'cuello de botella', colapsando con facilidad la ejecución de una consulta, si hay que procesar una cantidad considerable de registros. Una alternativa válida en determinados casos, consiste en incorporar, la tabla orígen de la subconsulta a la clausula From de la consulta principal, relacionando ambas tablas con un left join, mas la misma expresión utilizada en la cláusula Where de la subconsulta, para filtrar los resultados de la subconsulta a los registros relacionados de la consulta principal. Finalmente, para obtener el valor que nos devolvía la subconsulta, utilizamos una función de agregado.
    Ejemplo sobre la base de datos Neptuno.mdb (tabla Pedidos). Se trata de numerar los pedidos de cada cliente, según la fecha del pedido :

    Select
    (Select Count(*) From Pedidos As T
    Where T.IdCliente = Pedidos.IdCliente
    And
    T.FechaPedido < Pedidos.FechaPedido) + 1 As NºPedido_Cliente,
    IdPedido, IdCliente, FechaPedido
    From Pedidos;

    Select Pedidos.IdPedido, Pedidos.IdCliente, Pedidos.FechaPedido,
    Count(T.FechaPedido) + 1 As NºPedido_Cliente
    From Pedidos Left Join Pedidos As T
    On T.IdCliente = Pedidos.IdCliente And T.FechaPedido < Pedidos.FechaPedido
    Group By Pedidos.IdPedido, Pedidos.IdCliente, Pedidos.FechaPedido;

    Ambas consultas devuelven los mismos resultados, pero la segunda es 10 veces más rápida que la primera.
    Esta técnica de substitución de subconsultas la he empleado, dentro de la solución al reto, en 'Qry2' y la consulta final.
  4. Un conjunto de valores tal, que el resultado de todas las sumas posibles sea un valor único, irrepetible, y que la suma de los elementos menores de la lista, no pueda jamás alcanzar un elemento superior. Ejemplo : una lista de potencias 2, 4, 8, 16, 32, 64, etc o 3, 9, 27, 81, 243, etc.
Y esta es la solución :
CREATE TABLE t1 (id identity primary key, f_val Memo not null);
insert into t1 (f_val) values ('100');
insert into t1 (f_val) values ('7.4.250');
insert into t1 (f_val) values ('22.40.5.60.4.100.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.99.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.99.300.478.9999999');
insert into t1 (f_val) values ('10.30.40.50.20.30.40');
insert into t1 (f_val) values ('7.4.250');
insert into t1 (f_val) values ('-1');
insert into t1 (f_val) values ('-2');
insert into t1 (f_val) values ('-11');
insert into t1 (f_val) values ('-22' );
insert into t1 (f_val) values ('-123');
insert into t1 (f_val) values ('-321');
insert into t1 (f_val) values ('22.40.5.60.4.-100.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.-99.300.478.19710212');

Create Table Nums (Num Long Primary Key);
Insert Into Nums Values (0);
Insert Into Nums Values (1);
Insert Into Nums Values (2);
Insert Into Nums Values (3);
Insert Into Nums Values (4);
Insert Into Nums Values (5);
Insert Into Nums Values (6);
Insert Into Nums Values (7);
Insert Into Nums Values (8);
Insert Into Nums Values (9);
-- Descomponemos la cadena f_val en tantos registros como segmentos
-- (definidos por el caracter '.' como separador) tenga.
CREATE PROCEDURE Qry1 AS
SELECT id,
Num,
CLng(Mid(f_val,Num+1,Abs(InStr(Num+1,f_val & ".",".")-Num-1))) As Valor,
f_val
FROM
(
Select (TNums_1.Num*10)+ Nums.Num As Num
From Nums, Nums As TNums_1
) AS TNums_2, t1
WHERE Num<=Len(f_val)
And
(Mid(f_val,IIf(Num=0,1,Num),1) = "." Or Num=0);

-- Añadimos un campo 'Nivel' que nos indica el número de segmento
-- dentro de la cadena
CREATE PROCEDURE Qry2 AS
Select Qry1.Id, Qry1.Valor, Qry1.f_val, Count(T.Num) + 1 As Nivel
From Qry1 Left Join Qry1 As T
On Qry1.Id = T.Id And Qry1.Num > T.Num
Group By Qry1.Id, Qry1.Valor, Qry1.f_val, Qry1.Num;

-- El campo Orden se muestra solo a efectos didácticosSelect Id, f_val, Sum(Orden2) As Orden
From(
-- Calculamos la siguiente potencia :
-- El valor máximo de nivel (obtenido mediante una subconsulta)
-- lo elevamos al Nivel (en negativo).
-- Y dicha expresión la multiplicamos por Orden. La idea es obtener
-- una cifra tal, que la suma de todos los valores posibles para
-- 'niveles' más pequeños de la misma cadena, no la pueda superar.

Select Id, f_val, Orden * ((Select Max(Nivel) From Qry2) ^ (Nivel * -1)) As Orden2
From
(
-- Obtenemos para cada nivel, el número de registros más pequeños
-- según valor.
Select Qry2.Id, Qry2.Nivel, Qry2.f_val, Count(T.Id) As Orden
From Qry2 Left Join Qry2 As T
On Qry2.Nivel = T.Nivel And Qry2.Valor > T.Valor
Group By Qry2.Id, Qry2.Nivel, Qry2.f_val
)
)
Group By Id, f_val
Order By Sum(Orden2);

-- Eliminamos los objetos :Drop Procedure Qry2;
Drop Procedure Qry1;
Drop Table t1;
Drop Table Nums;

raipon. Terrassa a 03/01/2009.
 

Contactar