On Thu, Aug 3, 2023 at 4:25 AM Daniele Nicolodi <dani...@grinta.net> wrote:
> On 03/08/23 08:27, Eric Altendorf wrote: > > When you do something like > > SELECT account, SUM(position) ... > > > > you get a nice table where the list of positions for each account is > > grouped (with expand=true, it renders like a nested set of rows within > > the row) and each account name is only printed once. > > This is because the query compiler infers that you are running an > aggregate query and thus adds an implicit 'GROUP BY account' to it. > Thanks, that's what I thought aggregation was doing but I wasn't sure. > expand=true changes the representation of the inventory type returned by > sum(position) from an horizontal one to a vertical one, but does not > introduce any notion of grouping. > Right, I figured that was just a rendering feature. > > When you do something like > > SELECT id, date, narration, account, cost(position), > units(position)... > > > > You get one row per posting, and one transaction's information (e.g., > > ie, date, narration) gets repeated for each row. Visually this just > > makes it hard to see the grouping of postings in a transaction. > > A SELECT without an explicit FROM clause specifying a table is > implicitly run over the #postings table, which is just a concatenation > of the posting in all the transactions in the ledger. > Are you implying that if an explicit FROM clause specifies something else? I thought all SELECT queries were selecting from the postings table. In my case, I do happen to have a FROM clause, the full query is (now) more like: SELECT date, narration, account, units(position) as amount, round(number(cost(position)) / number(units(position)), 4) as cost_each, cost(position) as total_cost FROM has_account("PnL") and quarter(date) = "2021-Q1" > Is there a way to visually group postings in the same transaction, the > > way aggregated data is grouped when you do the SUM()? I'm not sure > > where this logic would belong; one could have a generic string CONCAT() > > aggregation operator I suppose, but that doesn't seem right, or one > > could hack it as an option into render_text(), but that also seems > awkward. > > The SELECT results set does not have any knowledge that the values in > the columns belong to postings and that these postings belong to > transactions. I don't see a way to implement what you are asking. > Yeah, I looked at the code and I think the cleanest way to do this would be to have the query engine, in addition to returning the dtype for each column, return a flag marking whether the column is sourced from a transaction or a posting. This could be a separate flag or maybe you could even work it into the existing `dtype` interface with a Generic as a marker type, e.g., instead of putting `datetime.date` in dtype, since it comes from the Transaction, you put in `TxCol[datetime.date]` for some `TxCol` wrapper/marker Generic. (But I'm guessing there, I'm not a Python expert.) The ColumnRenderer base class could unpack the nested type and dispatch as normal, but also set a flag saying that column renderers for TxCol types are eligible for ditto <https://en.wikipedia.org/wiki/Ditto_mark> behavior. Then, render_text() could take an "enable ditto" argument, and upon rendering, any ditto columns are only rendered in full in their first seen row, otherwise they are rendered as a ditto mark (or blank). I don't think this would be horrible, but I would also understand if you don't like the idea :) To customize the rendering of the results set, you can implement your > own beanquery.query_render.render_text() function. Postings are always > iterated grouped by transaction, thus if you know that you are always > going to have the 'id' column in the results set, you can just emit an > empty line every time the value in this column changes. > Thanks, I had taken a quick look there, but I'll look more closely. However, if you want to see postings grouped by transaction, maybe the > PRINT statement is what you want, instead of the SELECT statement. > I don't think that will let me do what I want, but it's a good point I'd forgotten about, so thanks for reminding me of that option. thanks, eric > > Cheers, > Dan > > -- > You received this message because you are subscribed to the Google Groups > "Beancount" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to beancount+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/beancount/fd01ff7f-7bae-bf87-fc62-4f900f5fbd2c%40grinta.net > . > -- You received this message because you are subscribed to the Google Groups "Beancount" group. To unsubscribe from this group and stop receiving emails from it, send an email to beancount+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAFXPr0toN13YyvAHG67Ufzd3ajieUop%2B-TPkpBR%2Bt43dHgbueQ%40mail.gmail.com.