From: Tony B. <ton...@ka...> - 2006-10-02 08:02:22
|
What's the current thinking on being able to write queries that include some sort of aggregate (SUM, COUNT, MIN, MAX, etc.)? As I believe I've mentioned here before (and have recently been blogging about[1]), our company now uses Semantic Mediawiki as its finance software. The data-entry side works really well, but most of the reporting we want involves digging directly into the database. I know I'll have to keep a close eye on changes to the underlying structure etc., but it's simple enough, and well worth it. However, there a few places where it would be really useful to be able to use some simple functions. Even just totals would be very useful. For example, we have <ask> queries that show all the Cheques deposited in a particular Bank Lodgement. On the Bank Statement this shows as a single line item, with a total, so to make it work we currently have to enter the total as an attribute of the Lodgement. This is possible to get wrong, however, so it would be nicer if there was some built in way to get the total of all the cheques in that Lodgement. Ideally we could use the result in an attribute of its own, so that it could be queried elsewhere: e.g. On page 'Lodgement 2039' [[lodgement total:=<ask headers="hide"> [[Category:Cheque]] [[lodgement::{{PAGENAME}}]] [[gross amount:=SUM(+)]] </ask> ]] But even if this isn't possible, it would still be handy to be able to add a row to the end of the "Show me all Cheques included in this lodgement" table that we already display on that page anyway. Then, even though we still need to enter the data, it will at least be more apparent when it doesn't match the actual total of the cheques. I'm not so sure what the syntax would be for specifying this, though. Thoughts? Thanks, Tony [1] http://nothing.tmtm.com/archives/2584 & previous (referenced within) |
From: Christopher B. <sli...@gm...> - 2006-10-04 03:05:25
|
ive been thinking on this myself for a while. im not sure exactly how it would be implimented, however. i would think the best way to specify it would be something like <ask format="table"> [[Category:Cheque]] [[lodgement::+]] [[lodgement::*]] [[amount:=+]] [[amount:=*]] [{SUM(amount)}] </ask> where [{...}] is a special code that tells the SMW parser to include that information at the end of the table. the attribute in the parenthesis would probably have to be a required attribute, just as it is when you specify order. or, alternatly (and probably easier to impliment), there could be another parameter passed to ask. something like <ask format="table" foot="SUM(%2)">...</ask> where "%2" referes to the second column of text displayed, not including the title (and would display below the second column. seperate with "|" maybe?). it could also be like <ask format="table" foot="SUM(%amount)">...</ask> although i believe this would be harder to impliment. if one of the developers (or a few users) deem this useful, i would be more than happy to attempt this and share my results. hope this was helpful Christopher Baker On 10/2/06, Tony Bowden <ton...@ka...> wrote: > > > What's the current thinking on being able to write queries that include > some sort of aggregate (SUM, COUNT, MIN, MAX, etc.)? > > As I believe I've mentioned here before (and have recently been blogging > about[1]), our company now uses Semantic Mediawiki as its finance > software. The data-entry side works really well, but most of the reporting > we want involves digging directly into the database. I know I'll have > to keep a close eye on changes to the underlying structure etc., but > it's simple enough, and well worth it. > > However, there a few places where it would be really useful to be able > to use some simple functions. Even just totals would be very useful. For > example, we have <ask> queries that show all the Cheques deposited in a > particular Bank Lodgement. On the Bank Statement this shows as a single > line item, with a total, so to make it work we currently have to enter > the total as an attribute of the Lodgement. This is possible to get > wrong, however, so it would be nicer if there was some built in way to > get the total of all the cheques in that Lodgement. > > Ideally we could use the result in an attribute of its own, so that it > could be queried elsewhere: > > e.g. > > On page 'Lodgement 2039' > > [[lodgement total:=<ask headers="hide"> > [[Category:Cheque]] > [[lodgement::{{PAGENAME}}]] > [[gross amount:=SUM(+)]] > </ask> > ]] > > But even if this isn't possible, it would still be handy to be able to > add a row to the end of the "Show me all Cheques included in this > lodgement" table that we already display on that page anyway. Then, even > though we still need to enter the data, it will at least be more > apparent when it doesn't match the actual total of the cheques. > > I'm not so sure what the syntax would be for specifying this, though. > > Thoughts? > > Thanks, > > Tony > > > [1] http://nothing.tmtm.com/archives/2584 & previous (referenced within) > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share > your > opinions on IT & business topics through brief surveys -- and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > |
From: Tony B. <ton...@ka...> - 2006-10-05 15:29:06
|
On Mon, Oct 02, 2006 at 11:16:18AM -0700, Christopher Baker wrote: > where [{...}] is a special code that tells the SMW parser to include that > information at the end of the table. the attribute in the parenthesis would > probably have to be a required attribute, just as it is when you specify > order. Interesting idea. Of course it would need to work where the display isn't a table (e.g where you're wanting to include result in text), but I don't see that being too much of a problem. > or, alternatly (and probably easier to impliment), there could be another > parameter passed to ask. something like > <ask format="table" foot="SUM(%2)">...</ask> > where "%2" referes to the second column of text displayed, not including the > title (and would display below the second column. seperate with "|" maybe?). I don't really like that. I fear it would be too difficult for non-programmers. SUM(amount) shouldn't really be much harder to implement than something like SUM(%2). This idea seems to be more tightly bound to the table output format, but also looks like it would start getting ugly where you want to apply functions to multiple columns. (For financial information you're often going to want at least three columns summed: net, tax, and gross). Thinking about this some more, most of where I would want to use this at the minute is as stand-along queries with a single result, rather than as a footer to other tables. I'm not too familiar with the innards, but it might be that this would be an easier first step than trying to introduce a lot of new syntax for table display? I'm trying to come up with Wikipedia examples for these sorts of functions, but most of what I can think of at the minute are rather too contrived. Do other people think this might be useful as well? Tony |
From: Denny V. <dv...@ai...> - 2006-10-06 18:12:32
|
Yes, I see the need for such features like sum, count, max, min, etc., and it seems worthwhile and straightforward to implement it, at least in the first thought. But I think I would go a slightly different way to implement it then suggested already. My idea would be, instead of extending <ask> to invent a new extension like <sum> and <count>. The advantages are twofold: -- easier to implement. <ask> is pretty complicated, really, and you neither want to make it more complicated than it is, nor do you want to burden yourself with the existing complexity. Just start fresh from the start. As it would be an extra file, it would also be easier to develop and distribute. -- easier to use. Instead of teaching someone <ask> and adding more and more features to it, you simply have a new keyword which can be used independent from it. Otherwise: more different simple commands than one complicated one. A suggestion for the syntax could be: <count>[[Category:Country]]</count> --> number of countries <count>[[Category:Country]] [[located in::Africa]]</count> --> number of African countries <count>[[Category:Country]] [[population:=>10,000,000]]</count> --> number of countries with a population of more than 10 Million <count>[[Category:Country]] [[population:=+]]</count> --> number of countries that have a population stated <sum>[[Category:Country]] [[population:=+]]</sum> Sum of the population of all countries. Should be the same as <ask>[[World]] [[population:=*]]</ask> :) Together with parser functions <http://meta.wikimedia.org/wiki/ParserFunctions> you probably could also make some calculations, like getting the average population of African countries, although this wouldn't look too nice, I am afraid: {{#expr:<sum>[[Category:Country]] [[located in::Africa]] [[population:=+]]</sum>/<count>[[Category:Country]] [[located in::Africa]] [[population:=+]]</sum>}} Hmm... thinking about that, maybe something like <average>[[Category:Country]] [[located in::Africa]] [[population:=+]]</average> would be nicer... With the parser functions I guess you could actually make even little programs within the wiki, since you have things like comparators, if/else-branching etc. But this is probably more something for hardcore users. Still, it would be an idea. But, as said, just a suggestion. Cheers, denny Tony Bowden wrote: > On Mon, Oct 02, 2006 at 11:16:18AM -0700, Christopher Baker wrote: >> where [{...}] is a special code that tells the SMW parser to include that >> information at the end of the table. the attribute in the parenthesis would >> probably have to be a required attribute, just as it is when you specify >> order. > > Interesting idea. Of course it would need to work where the display > isn't a table (e.g where you're wanting to include result in text), but > I don't see that being too much of a problem. > >> or, alternatly (and probably easier to impliment), there could be another >> parameter passed to ask. something like >> <ask format="table" foot="SUM(%2)">...</ask> >> where "%2" referes to the second column of text displayed, not including the >> title (and would display below the second column. seperate with "|" maybe?). > > I don't really like that. I fear it would be too difficult for > non-programmers. SUM(amount) shouldn't really be much harder to implement > than something like SUM(%2). This idea seems to be more tightly > bound to the table output format, but also looks like it would start > getting ugly where you want to apply functions to multiple columns. (For > financial information you're often going to want at least three columns > summed: net, tax, and gross). > > Thinking about this some more, most of where I would want to use this at > the minute is as stand-along queries with a single result, rather than > as a footer to other tables. I'm not too familiar with the innards, but > it might be that this would be an easier first step than trying to > introduce a lot of new syntax for table display? > > I'm trying to come up with Wikipedia examples for these sorts of > functions, but most of what I can think of at the minute are rather > too contrived. > > Do other people think this might be useful as well? > > Tony > > > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys -- and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel |
From: Tony B. <ton...@ka...> - 2006-10-09 06:46:23
|
On Fri, Oct 06, 2006 at 08:12:22PM +0200, Denny Vrandecic wrote: > My idea would be, instead of extending <ask> to invent a new extension > like <sum> and <count>. That's a very interesting idea, and it certainly lets us try to build this separately and see about feeding it back in later. From a cursory glance at the existing code it's going to be tricky to reuse the code other than through cut'n'paste, so I guess the first thing is to just get something working and then see if we can abstract the common bits out when finished? Tony |
From: Denny V. <dv...@ai...> - 2006-10-09 07:34:15
|
Tony Bowden wrote: > On Fri, Oct 06, 2006 at 08:12:22PM +0200, Denny Vrandecic wrote: >> My idea would be, instead of extending <ask> to invent a new extension >> like <sum> and <count>. > > That's a very interesting idea, and it certainly lets us try to build > this separately and see about feeding it back in later. From a cursory > glance at the existing code it's going to be tricky to reuse the code > other than through cut'n'paste, so I guess the first thing is to just > get something working and then see if we can abstract the common bits > out when finished? > > Tony That's how I would approach it. My feeling is that this will be *far* simpler than the current inline queries, and looking at them too hard will only make the resulting code more complex rather than as simple as possible. So, a bit copy and paste to get it started, and later refactor to have less code repetition. Or else there is someone who was enough insight to provide an architecture for this up front, but I doubt it :) cheers, denny |