det finns ett gammalt skämt om flytande aritmetik:

”om jag skär en tårta i tre är varje bit 0,33 av kakan. Om jag håller alla tre bitarna ihop igen ger det mig 0,99 av kakan. Vart tog resten av kakan vägen?”
— ”enkelt. Det är den lilla biten som sitter fast på kniven”

Flytpunktsaritmetik handlar om att tolerera och hantera approximation för att undvika överflödesfel i beräkningarna. I den verkliga världen bryr vi oss vanligtvis om precision i antal och kommer istället att offra utrymme och resurser för att undvika överflöd.

medan vetenskapen arbetar lyckligt inom en felmarginal, är precision viktigt i affärsredovisning. När jag var en cub-programmerare skrev jag en gång vad jag trodde vara ett perfekt lämpligt sätt att beräkna vinsten för börsmäklare. I en miljon pund var det högst ett öre eller två. Jag var mycket nöjd. Den använde beräkningarna som var inneboende i PL / 1-kompilatorn som vi använde vid den tiden för att utveckla finansiella paket. Jag visade dem den fint utformade applikationen, och de var förskräckta. Ett öre ut i en miljon pund verkade de hårdkokta stadshandlarna vara hänsynslösa. De skulle inte ha det. Jag var tvungen att skriva ett binärt kodat decimal (BCD)-paket i assembler-kod som var exakt korrekt.

SQL Prompt har en kodanalysregel (BP023) som varnar dig för användningen avFLOAT ellerREAL datatyper, på grund av de betydande felaktigheter de kan introducera för den typ av beräkningar som många organisationer rutinmässigt kommer att utföra på sina SQL Server-data.

ungefärliga datatyper

flytande aritmetik utformades vid en tidpunkt då det var en prioritet att spara minne samtidigt som det gav ett mångsidigt sätt att göra beräkningar som involverade stora tal. Även om det fortfarande är användbart för många typer av vetenskapliga beräkningar, särskilt de som överensstämmer med dubbel precision IEEE 754-standarden för flytande punktaritmetik, är det av nödvändighet en kompromiss. Ledtråden är i namnet på denna typ av data och aritmetik: ’ungefärlig’. Flyttal kan inte exakt representera alla reella tal: dessutom kan flyttalsoperationer inte exakt representera alla aritmetiska operationer. Storleken på antalet som de kan hålla är dock mycket större än vad som är möjligt i andra numeriska typer, även om det inte alltid hålls korrekt.

de problem som uppstår vid användning av flytpunktsberäkningar beror på avrundning under komplexa beräkningar och ses oftast om data är ’dåligt konditionerade’, så att små förändringar i ingången förstoras i utgången. Felaktigheterna är mycket mindre uppenbara med ökad precision av representationen av siffrorna, men de är fortfarande närvarande, ändå. Det finns också några esoteriska begränsningar i användningen av siffror som är giltiga men inte kan representeras i flytpunkt, till exempel tan(kub/2), men dessa kommer sannolikt att excitera endast matematiker.

SQL Server flyttal datatyper

SQL-standarden har tre flyttal, ungefärliga datatyper, REALDOUBLEPRECISION och FLOAT(n). SQL Server överensstämmer med detta förutom att det inte har någon DOUBLEPRECISION datatyp, med FLOAT(53) istället. FLOAT(24) ochFLOAT(53) datatyper motsvarar Binary32 (singel) och Binary64 (dubbel) i IEEE 754-standarden och lagras i 4 och 8 byte och 7 och 16 siffror hålls i enlighet därmed. De är användbara när det är viktigt att beräkningar ger samma resultat som en applikation som använder.NET framework som också använder IEEE 754. Den dubbla precisionstypen krävs också när siffror överstiger i sin storlek det maximala tillåtna av DECIMAL datatyp (38 siffror) men med förlust i precision. Ungefärliga siffror kan naturligtvis inte användas på ett tillförlitligt sätt i något test av jämlikhet, till exempel en WHERE klausul.

beräkningar med den verkliga datatypen (enkel precision)

Jag kommer att provaREAL datatyp. FLOAT(24) datatyp, eller mindre, reagerar på samma sätt. Det första du bör komma ihåg när du experimenterar med flyttal i SQL Server är att SSMS gör ett flyttal på ett sätt som döljer små skillnader. Till exempel:

…ger 0.1

för att se mer exakt vilket värde som lagras i ett flytpunktsnummer måste du använda funktionen STR() och ange den precision du faktiskt vill ha.

redan är detta alarmerande. Vi har ju trots allt att göra med data med miljontals rader så små fel kommer att staplas upp om de, som bankirer avrundning, genomsnitt ut. Det felet är redan nära ’ penny in a million pounds ’(1/ 240000000) som jag nämnde i introduktionen!

låt oss undvika 0.1 och lägga ner det till ett freak av flytpunkt. Vad sägs om att dela 1 med 3. Visst kan detta inte vara ett problem?

hoppsan. Det blev fel. OK, det är ett litet fel men kom ihåg min historia om bankirerna. Ett svar är antingen rätt eller fel, det finns inga nyanser av grått för män i grå kostymer. I Handelshögskolan finns det bara ett fält och ett kors. Inget tecken som betyder ’nära nog’.

ett enkelt test är att dela en med siffror från en till tjugo. Vad kan gå fel?

vi kan lagra resultaten av flytpunkten och numerisk beräkning, båda konverterade till strängar och vi jämför sedan strängarna (varnas för att STR() kan sätta i ett ledande utrymme vilket gör det till en komplikation).

Now, what if we list the rows where the numbers don’t match?

Ugh! Endast där divisorn var 1, 2, 4, 8 eller 16 var det ett korrekt resultat.

om du hoppas att flottören på något sätt var korrekt, och den numeriska versionen inte var, här är den numeriska kvoten beräknad i Excel:

beräkningar med FLOAT(25) eller över (dubbel precision)

Om du använder dubbel precision flyttal, FLOAT(25) eller över, testerna är alla passerade, på grund av STR() funktionen tillåter maximalt sexton platser till höger om decimalpunkten. Om det finns fler än 16 är resultatet avkortat. Den dubbla precisionsdatatypen har sexton siffror, medan den enda precisionsdatatypen har sju. Du har också sett att den enda precisionsdatatypen får de första sju siffrorna rätt. På samma sätt får dubbelprecisionen de första sexton siffrorna rätt. Vi kan bara bredda antalet för att se approximationen.

den fraktionerade delen har försvunnit, eller hur? Det är förmodligen bara en liten skillnad, men i vissa beräkningar kan det orsaka problem.

slutsats

Flyttalsaritmetik är snabb och ekonomisk vid lagring men ger ett ungefärligt resultat. Den är lämplig för välkonditionerade vetenskapliga tillämpningar men inte för ekonomiska beräkningar, vilket kräver att ett tal är antingen ’rätt’ eller ’fel’. Det har också den extra nackdelen i en databas eftersom du inte på ett tillförlitligt och konsekvent sätt kan testa två ungefärliga siffror för jämlikhet.

det är inte korrekt att säga att du aldrig ska använda flytande punktnummer i SQL-datatyper eller i aritmetik. Ungefärliga typer finns i SQL-standarden för ett ändamål. Jag skulle numera alltid hålla fast vid dubbel precision flyttal datatyp i SQL Server, där det finns ett lämpligt krav. De är bra för sådana ändamål som modellering av vädersystem eller plottning av banor, men inte för de typer av beräkningar som den genomsnittliga organisationen sannolikt kommer att använda en databas för.

om du upptäcker felaktig användning av dessa typer, bör du byta till en lämplig DECIMALNUMERIC – typ istället. Om du vet att du behöver flytpunkt aritmetik och kan förklara varför, vet du förmodligen tillräckligt för att undvika fallgroparna i flytpunkt, till exempel den som inträffade i det berömda Patriot-missilfelet som ledde direkt till 28 dödsfall.