How To Get The Most Frequently Used Column Values
Whenever I import external data, integrate to another database, or am new to a project, I need to get familiar with the database. The table schemas, relational integrity, and constraints are the first thing I look at and take me a long way, but soon I need to know what the data looks like.
In an ideal world, relational integrity and database constraints would define control this, and all I’d really need to do is look at those. But the reality is, in 15 years of working in this industry, most of the databases I’ve worked on, that I didn’t design, have barely used constraints and some haven’t even used relation integrity fully!
The need to get a good feel of the data is even more prevalent when working with dirty data, or when refactoring poorly written applications to ensure any refactoring doesn’t introduce other issues. I will usually wind up writing the following query repeatedly:
Select column_name, count(*) From table_name Group by column_name Order by count(*) desc, column_name
This little query often reveals; inconsistencies between data and the application, where an application sets column X to possible values of ‘A’, ’B’, ‘C’, ‘D’, or ‘E’, but in reality, there may be zero ‘C’ and ‘E’ values in that column, but there is 6 ‘X’s, 1 ‘Q’, and an ‘?’. Or I may find that there are only 6 rows with data in that column, out of almost 3 million rows, indicating the column / application feature is unused.
Anyway, yesterday I finally wrote a little stored procedure which will print out the most frequent N values in each column for a specified table.
/* Purpose : Retrieves the top N most frequent values in each column of the specified data table. Copyright © Jaten Systems Inc. 2009, All rights reserved http://www.jatensystems.com/ https://whileicompile.wordpress.com/ http://www.johnmacintyre.ca/ */ create proc jaten_diag_GetTableContents (@tblName nvarchar(200), @rowCount int) as begin declare @colId int; declare @colName nvarchar(100); declare @sql nvarchar(2048); declare @tblRowCount int; -- cursor to get columns DECLARE curColumns CURSOR FAST_FORWARD FOR SELECT colid, [name] FROM syscolumns where id=object_id(@tblName) order by colid open curColumns; -- get table row count set @sql = 'select @retCount=count(*) from ' + @tblName; exec sp_executeSQL @sql, N'@retCount int OUTPUT', @retCount = @tblRowCount OUTPUT; -- print table header print ''; print '---------------------------------'; print '--- ' + @tblName; print '--- Row count : ' + cast(@tblRowCount as nvarchar) print '---------------------------------'; -- get info for each column fetch next from curColumns into @colId, @colName; while 0 = @@fetch_status begin -- print column header print ''; print '---------------------------------'; print '--- Column [' + cast(@colId as nvarchar) + '] - ' + @colName + ' ---'; -- compile & execute grouping sql select @sql = 'select top ' + cast(@rowCount as nvarchar) + ' count(*) as [count], ' + ' cast(((count(*) * 100)/' + cast( @tblRowCount as nvarchar) + ') as nvarchar) + ''%'' as [Percentage], ' + ' [' + @colName + '] as [col_value] ' + 'from ' + @tblName + ' ' + 'group by [' + @colName + '] ' + 'order by count(*) desc, [' + @colName + ']'; exec sp_executeSQL @sql; --print @sql; -- next fetch next from curColumns into @colId, @colName; end -- clean up close curColumns; deallocate curColumns; end
Please note 2 things :
1. You need to run it with ‘Results to Text’ or ‘Results to File’ setting.
2. The table parameter will need square brackets if the table name uses unconventional characters.
If you create it and run it in AdventureWorks on the ‘Production.Product’ table
exec jaten_diag_GetTableContents 'Production.Product', 5
… you will get these results
— Row count : 504
— Column  – ProductID —
count Percentage col_value
———– ——————————- ———–
1 0% 1
1 0% 2
1 0% 3
1 0% 4
1 0% 316
(5 row(s) affected)
— Column  – Color —
count Percentage col_value
———– ——————————- —————
248 49% NULL
93 18% Black
43 8% Silver
38 7% Red
36 7% Yellow
(5 row(s) affected)
Notice how the Color column reveals that almost half of the products do not have a color setting? This could imply relevancy or this data possibly has a problem being maintained. But also, notice how unique columns will obviously provide meaningless data.
The AdventureWorks database is a very clean database, so this example is a bit contrived, but in the real world, there are plenty of databases where this little procedure will allow you to get some insight into the data.
How do you get familiar with new data?
Copyright © John MacIntyre 2009, All rights reserved
WARNING – All source code is written to demonstrate the current concept. It may be unsafe and not exactly optimal.
No comments yet.