Sexe aléatoire, nullité et Sql

Avec un titre comme ça, je suis sûr d’avoir 2 ou 3 lecteurs. Je lis actuellement « Sql for smarties » de Joe Celko. Tout n’est pas parfait dans ce livre, mais il y a certaines choses amusantes que je découvre avec plaisir : par exemple que le genre est normalisé par ISO 5218 « Information technology — Codes for the representation of human sexes ». Il s’agit d’un chiffre unique désigné dans le standard par « SEX » dont les quatre valeurs possibles sont :

  • 0 = inconnu (unknown),
  • 1 = homme (male),
  • 2 = femelle (female),
  • 9 = non applicable (not applicable).

Note 1 : Les rédacteurs du standard ont pris la peine de préciser que le fait que Homme était 1 et la Femme 2 n’avait aucune signification particulière... que cela était dû aux pays qui ont « poussé » ce standard. Et vous reconnaissez le premier chiffre du numéro de sécurité sociale français : c’est une preuve irréfutable du rayonnement culturel de la France dans le monde.

Note 2 : Sexe « non applicable » ne recouvre pas d’accident biologique particulier, il s’agit d’une valeur à utiliser pour des personnes morales (une entreprise n’est donc ni homme ni femme – bien au contraire aurait ajouté Pierre Dac).

Note 3 : Je viens, l’air de rien, de vous faire économiser 92 francs suisses, soit environ 65 euros. C’est en effet le prix à payer pour le PDF officiel de la norme ISO 5218. C’est scandaleux mais c’est comme ça : les standard ISO sont payants.

Revenons au SEX (avec ce billet, ce blog va perdre des points de ranking) et à SQL pour insister sur « l’inconnu » et le « non applicable ». Ces deux notions se cachent habituellement derrière le NULL, ici elles ont été explicitées et c’est une bonne chose d’un point de vue lisibilité/expressivité ainsi qu’en terme de mise en œuvre (moins on utilise de colonne NULLABLE, mieux on se porte et je ne vais pas faire ici le topo classique de la logique ternaire et des problèmes subtils introduits par le NULL car on les trouve partout).

Une autre découverte pour moi de la lecture du livre de Joe Celko est un aspect de l’instruction CASE que j’ignorais. Supposons que l’on veuille initialiser une valeur aléatoire pour un SEX. Nous avons 4 valeurs à tirer au sort (de façon équiprobable), le code suivant semble correct :

select case CAST( (4*rand()) as int )
          when 0 then '0' -- Unknown
          when 1 then '1' -- Male
          when 2 then '2' -- Female
          when 3 then '9' -- Unapplicable
       end

Et pourtant il ne l’est pas. Ci-dessous un test tout bête : il remplit 200 000 lignes d’une table avec une colonne SEX aléatoire selon le code précédent.

create table dbo.tTest( Sex char );
-- Caution: Declare and initialize in the same statement like this 
-- works only on Sql Server 2008, not 2005. 
declare @i int = 200000; 
while @i > 0
begin
	insert into dbo.tTest( Sex )
		select	case CAST( (4*rand()) as int )
					when 0 then '0' -- Unknown
					when 1 then '1' -- Male
					when 2 then '2' -- Female
					when 3 then '9' -- Unapplicable
				end
	set @i = @i-1;
end

Et on affiche le contenu de la table résultante avec le compte et la probabilité résultante.

select 	Sex, 
        COUNT(*), 
        Prob = cast(COUNT(*) as float)/200000 
    from dbo.tTest
   group by Sex
   order by 1;

Première observation : NULL apparait.

Deuxième constat : les nombres respectifs de valeurs (et donc leurs probabilités) ne sont pas équitablement répartis.

SexCountProb
NULL 63338 0,31669
0 50067 0,250335
1 37320 0,1866
2 27918 0,13959
9 21357 0,106785

Les matheux auront compris. L’instruction CASE est, en fait, réécrite ainsi :

select	case 
		when CAST( (4*rand()) as int ) = 0 then '0' -- Unknown
		when CAST( (4*rand()) as int ) = 1 then '1' -- Male
		when CAST( (4*rand()) as int ) = 2 then '2' -- Female
		when CAST( (4*rand()) as int ) = 3 then '9' -- Unapplicable
	end

Ce qui ne correspond pas du tout à l’idée initiale, et pouf pas marche.

La solution est évidemment de capturer la valeur fournie par la fonction rand() dans une variable:

declare @p int = CAST( (4*rand()) as int );
select	case @p
			when 0 then '0' -- Unknown
			when 1 then '1' -- Male
			when 2 then '2' -- Female
			when 3 then '9' -- Unapplicable
		end

N'incriminez pas SQL Server : ce sont les spécifications de SQL. C’est comme ça et pas autrement que le CASE doit être implémenté, la forme « classique » du switch-case que l’on connait dans de nombreux langages n’est pas celle réellement supportée par SQL, elle n’est qu’un sucre syntaxique autour du « râteau de si ». SQL ne connait vraiment que le râteau !

Pour finir, un exercice : montrer en calculant les probabilités formellement que le comportement observé (NULL et probabilités différentes) est parfaitement logique.

Ajouter un commentaire

  Country flag

biuquote
  • Commentaire
  • Prévisualiser
Loading